Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Project Revenue Review Formula
Comments

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

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

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.

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 (MarJun) or is it more complicated than that?
Craig

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.

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

WOW! That is perfect. Thank you!!
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 465 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives