Accumulative Formula Required

whitney.thomas

Hello!

We require a formula that calculates the PRODUCTION PACE for the completed builds in a financial year quarter.

Currently we have the Pace calculating off the completed builds/ number of days in the qtr, but this means that the pace is really high compared to how often we are actually completing a build.

I want to create an accumlitve formula that counts the amount of days from the start of each qtr, and calculates the days past out of the total so that the pace of the completed buidsl is accurate.

A formula that i tried to make work was the below but it doesnt come up with anything (0).

=COUNTIFS(WORKDAY(DATE(2023, 4, 1), [# of Days]@row, {Holidays}) <= TODAY(), MONTH(WORKDAY(DATE(2023, 4, 1), [# of Days]@row, {Holidays})) = MONTH(TODAY()))

Anyone able to give us a hand in arranging this formula?

Appreciate the help :)

Whitney

Jgorsich

You are missing some commas in your formula

countifs is arranged with the range, then a comma, then the criteria, then the next range, then a comma, etc.

Looking at your first assessment, for example, you've got this:

WORKDAY(DATE(2023, 4, 1), [# of Days]@row, {Holidays}) <= TODAY()

That puts your "⇐Today()" as part of the range. You need a comma after your close your workday() function before your "⇐today()" criteria.

Additionally, what you are assessing here, in plain language is this:

Count all the rows where the date X days past 4/1/23 is before today (where x is yoru [# of Days}@row AND where the name of the month that date is in is the same as the name of the current month.

I'm having trouble assessing precisely what you are trying to do, but it doesn't seem like that is it. It seems like your base argument is "Just because we completed 3 projects in a 60 day quarter doesn't mean we are completing projects in 20 days; each of those projects may have taken WAY longer than that", but I'm not really sure. "Pace" could be defined as 2 different things - the average number of days required per project OR the frequency with which you are creating projects. Specifically, this would break down to saying "Projects completed this quarter took, on average, 1.5 years to complete" vs "We were completing projects every 20 days during this quarter, though everything that was completed was started much earlier". You can see that those are two very different statements.

For the first, you'd need a per project break down that noted the start date and the end date. From that you could get the duration and then average those durations that came from projects completed in the quarter you are assessing.

For the second, doing it the way you currently are seems correct.

whitney.thomas

Hey! thanks for your response @Jgorsich , i appreciate the time put in it!

I don't really understand your explanation tbh, but ill try explain a bit more in depth what i need :)

So currently we have the production pace formula calculating from the builds divided by number of days within each financial quarter (april - june = qtr 1, july - sep = qtr2 etc.), but unfortunately as it is dividing by the total number of days within the WHOLE qtr, it calculates the production pace based off the whole qtr, not jsut the accumulative amount of days that have passed within the total of days for the qtr. This results in our actual pace being higher than it is in reality.

So what i need to add into the formula, is that it calculate the total number of builds completed so far, within the amount of days that we have used up so far in the current qtr.

Sorry, does this make any more sense?

Jgorsich

That does help clear it up - it sounds like your issue is that the CURRENT quarter is pretty much always inaccurate, but the others are fine.

If that is the case, solving it will be easiest to understand by adding a lot of columns. You can combine these all later (just nest all the formulas) but start with it spread out and it will be way easier. Incidentally, you'll want to ultimately wrap all of these with some kind of if statement or iferror to prevent them from having issues on empty rows, but I'm leaving out that detail.

First, you need to identify the starting date and ending date of each quarter. Make 3 columns - call the first "quarter", the second "start", and the third "end". Start and end both need to be date columns. For quarter, the formula needs to be something like "=if(value(mid(status@row,2,1))=4,1,value(mid(status@row,2,1))×3+1)", this should just extract the number of the quarter, then turn it into a month by checking to see if it is quarter 4 and otherwise multiplying by 3 and adding 1. (I'm doing this from my phone and not testing it, so the value() function may be overkill and not needed - if it doesn't work, remove that and see if it does).

For start, you need to create the date that starts the relevant quarter. This should be something along the lines of "=date(value(left(status@row,4)),quarter@row,1)". (Same note on value().). You can see, this should be pulling your year from from your status, grabbing the starting month of the quarter, and creating the date for the first day.

For end, the formula is basically the same, "=date(value(left(status@row,4)),quarter@row+3,1)-1". You can see the goal here is to get the first date of the NEXT quarter and then subtract 1 day. That way you don't need to stress about the number of days in the ending month.

Now, you need to identify the current quarter. Make a new column called "current", the formula should be something like "=if(and(today()>=start@row, today()⇐end@row),1,0)". This is just an if statement to identify all rows reflecting the current quarter with a 1 by checking to make sure that today is between the two dates.

Now, you need to identify the total workdays in the quarter, while adjusting for the current one, in a new column called "duration":

"=If(current=0, networkdays(start@row,end@row,{holidays}),networkdays (start@row,today(),{holidays}))"

you can see, this is just using networkdays() to get the total days available and using your new current column to decide whether the end date is the end of the quarter or today.

Now, finally, you can just divide you number of days by projects for your pace!

I hope that works for you!

Jgorsich

I just realized, my formula for the end date will break on quarter 3… try this instead for the end column:

"=date(value(left(status@row,4)),quarter@row+3,1)-1"

"=date(year(start@row+95),month(start@row+95),1)-1"

This should basically grab you the first day of the month 95 days after your start and then subtract one. Same basic concept as originally, but handles the wrap from December to January.