Project Revenue Review Formula

Jill Youngberg

Jill Youngberg

I have made no progress on this. Hoping someone can help!!

J. Craig Williams

Jill,

 

I have lots of questions and your screen shot did not answer them.

What value do you expect in the selected cell in your screen shot?

 

Craig

 

Jill Youngberg

Sorry for the selected cell - you can ignore that one.

 

I am trying to find a way to show how much money is brought in during each month of construction. Construction for the "Lewis" project only lasts 3 months. I can't find a way to have the formula only go for a certain number of months based on the # of months of construction.

J. Craig Williams

Jill,

Did you solve this yet?

If not, I'm still at a loss on what you are trying to do.

Can you post a screen shot of your column headers?

It looks like your [Calc Months] column may be using a formula like this:

=( [end date]  - [start date]) / 28

For 

Dr Lewis, is the total amount $519,167 for the nearly 4 months?

Are you trying to calculate $129,791.75 for each of the 4 months (Mar-Jun) or is it more complicated than that?

Craig

Jill Youngberg

Thanks for checking - no I have yet to be able to figure this one out. And honestly it may be mostly confusing due to how I am trying to explain it.

 

I am trying to calculate the $ that is forecasted to be brought in each month per project. For example - Dr. Lewis is a total of $519,167. The construction for that project is roughly 4 months long. I want to have a formula that can tell based on the # of months of the project how much it is bringing in. The formula for the January column would state that if the project started in January it would calculate the amount per month knowing that it will only last for 4 months.... It would show $129,791 for months January, February, March, and April. and in May it would show $0

 

See the attached for column headers.

 

Sorry I am not sure this is making any sense still.

J. Craig Williams

Jill,

That's what I thought you were going for, but did not want to invest time in doing the wrong solution.

1. Add or update a row with the numbers of the months columns, for example on the row that has "Metrics" listed. Just the number is fine.

2. Add this formula to Dr Lewis's January column:

=IF(AND(Jan$3 >= MONTH($ConStart4), Jan$3 <= MONTH($ConFin4)), $[Total Amount]4 / (MONTH($ConFin4) - MONTH($ConStart4) + 1))

Change the 3's to the row where you month numbers are.

Change the 4's to Dr Lewis's row

Add back in your IFERROR() checks

3. Copy the formula to the rest of the months and to the other contractor's rows.

Explanation:

$[Column] like $ConFin4 sets the absolute column reference so when you copy to another column, the formula still looks at the ConFin column.

$3 like Jan$3 sets the absolute row reference so when you copy to another row, the formula still finds the month number.

These two together allow you to edit one formula and then copy it everywhere you need it.

Note I use the range of the months + 1 to determine what the contract months are instead of looking at [Calc Months] because it simplifies things.

(MONTH($ConFin4) - MONTH($ConStart4) + 1)

Lastly, any time you do things like this, it is likely there will be a round off error.

For example, if the total is 334,000.00, and the contract duration is 3 months, the totals of the months will add up to 333999.99 if the months are rounded to the cent and 333999.00 if rounded to the $.

Hope that helps.

Craig

 

 

 

 

 

 

Jill Youngberg

WOW! That is perfect. Thank you!!