Formula to assist in total spread over months

[daysamuel]

In need of the correct formulas to use in order to automatically show data (monetary spending) across 12 months depending on values entered on which months money was spent in.

Please see attached spreadsheet for further detail on this.

Any help is genuinely appreciated

Sam

[philaugust2004]

Can you clarify the end month rule? In your example you have start month of 6 and end month of 11 and that seems to be for months 6,7,8,9 and 10 .ie excluding 11. So in row 6 are months 1 to 12 meant to exclude or include month 12?

[daysamuel]

Can you clarify the end month rule? In your example you have start month of 6 and end month of 11 and that seems to be for months 6,7,8,9 and 10 .ie excluding 11. So in row 6 are months 1 to 12 meant to exclude or include month 12?

Many apologies, now you have pointed this out its an obvious flaw. But you are correct, starting on month 1 and ending on month 12 does infact mean it runs for 12 months and starting on month 6 and ending on month 11 should actually run for 6 months including the end month

Thanks

[avk]

Refer attach file.
You are calculating Total duration wrong. Start Month & End Month total duration is 6 (As per your example row number 10)
Corrected formula is

=SUM(D10-C10)+1

I added one column for Monthly Amt (column "F")
In "G6"

=IF(G$4=MEDIAN($C6,$D6,G$4),$F6,0)

[daysamuel]

Refer attach file.
You are calculating Total duration wrong. Start Month & End Month total duration is 6 (As per your example row number 10)
Corrected formula is

=SUM(D10-C10)+1

I added one column for Monthly Amt (column "F")
In "G6"

=IF(G$4=MEDIAN($C6,$D6,G$4),$F6,0)

Thank a lot that helps me get the base down for the spreadsheet I'm creating.

What would be the process of amending the formula to say a ratio of spending would be different each month? (I believe these are S curves?) Say the spend ration over the duration starts with low amounts and gets more and more each month. I assume a ratio would have to be provided?

[avk]

Simple ratio calculation is (monthly Amount*100)/Total Amount

=SUM(G6*100)/$B6

format as percentage.