Formula to spread amount

[apple99]

Hi,

Would appreciate some help to formula column E to AB, to split amount in column A based on no. of months (column D) and from date range column B (start) & end (column C).

For current formula used, there's a balance - see column AC, how to split amount so that when totals up across months = amount in column A.

Manual input from column A to C only.

Thanks.

[Estevaoba]

Try in D2 and copied down
=YEARFRAC(B2,C2)*12

In E2 copied across and down
=IF(AND(E$1>=$B2,E$1<=$C2),ROUND($A2/$D2,2),"")

Good luck!

[apple99]

Hi,

Thanks for the reply but there's still a variance of 0.02 which needs to be allocate to any months, can you help on that?

For E2, 6 months totals to 11,500.02, I need to amount to be exactly the same as in column A.

[Estevaoba]

Maybe in E2:

Please Login or Register  to view this content.