I am creating a tool where an S-curve chart is generated based on a table of information to calculate forecast cashflow across the number of months the activity will occur for.
Everything is working, except the only way I can get a value to calculate is to level the value across the number of months, (eg if the activity occurs for 5 months, the calculation is an even 20% per month), however this is not reflectiove of the true s-curve style of the projects I manage.
Is there a simple way to incorporate S-Curve into my formula to more accurately reflect the heightened activity through the centre of a time period?
Current formula reads:
=IF(AND(M$17>=$B20,M$17<=$C20),$F20/$E20,"")
M17 = Month of expenditure
C20 = End date of activity
F20 = % of Project value assigned to the activity
E20 = Number of months the activity is planned to run for
I need the F20/E20 portion of the formula to better reflect a ramp up and ramp down scenario.
Alternatively, I can create a Lookup to another sheet, if someone has a tried and tested formula to generate percentages in the same fashion for projects ranging from 1 to 60 months in duration that I could use to create a new data set.
Thanks so much
