Interactive s-Curve

[RESAT1992]

I have the following formula spreading cost of each line item across the column of months.

where:

AT32 = choice between "Lump" - do nothing, "Flat" - linear distribution or "S-Curve" - spread as an s-curve (this is a drop down box)
AX14 is the months Jan-17 for money to be spread. (A row of project dates)
AR32 = A Line item start date
AS32 = Line item finish date
AV13 = standard deviation of 6
AU32 = No of Months (AS32 - AR32)
AV32 = Average Cost per month
V32 = total Line Item Cost

=IF($AT32="Lump",0,IF(AND($AT32="Flat",AX$14>=$AR32,AX$14<=$AS32),$V32/$AU32,0)+IF(AND($AT32="S-Curve",AX$14>=$AR32,AX$14<=$AS32),(NORM.DIST((YEAR(AX$14)-YEAR($AR32))*12+MONTH(AX$14)-MONTH($AR32)+1,$AU32/2,$AV$13,TRUE)-NORM.DIST((YEAR(AX$14)-YEAR($AR32))*12+MONTH(AX$14)-MONTH($AR32),$AU32/2,$AV$13,TRUE))/(1-2*NORM.DIST(0,$AU32/2,$AV$13,TRUE))*$V32,0))


I would like this formula to spread the costs better in that the example below shows £16,519 for Feb-17 but the start date only allows 4 days in Feb. and July 17 there is nothing. It would be good to spread based on days and allocate cost in part months.

Is there a way to refine this to better spread the costs, especially for mid-month starts and finishes.

Thanks

S-Curve

St Dev

6

Activity START

Activity FINISH

Lump

"Manually Populate date for Lump Sum Payments"

Check Sum

Jan-17

Feb-17

Mar-17

Apr-17

May-17

Jun-17

Jul-17

Flat

Months

Cost per Month

25-Feb-17

21-Jul-17

S-Curve

5.00

16,987

OK !

-

16,519

17,220

17,460

17,220

16,519

-

<colgroup><col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 3680;" span="2"> <col width="73" style="width: 55pt;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 2784;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 2784;"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4064;"> <col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3232;"> <col width="119" style="width: 89pt; mso-width-source: userset; mso-width-alt: 3808;" span="6"> <tbody> </tbody>