Add growth to formula that uses structured [Table] references

[kcmuppet]

Hello experts,

I've got a IFS formula that spreads costs in different ways according to what is selected in a table next to the cells containing the formulae

(row 6 contains the first day of the month, row 7 the last day and 8 the year and month only)

Forecast Model Example.xlsx
	D	E	F	G	H	I	J	K
91						May-23	Jun-23	Jul-23
92	Annual Growth rate	Repeats	Start	End	Amount
102	0%	End of quarter	01 May 23	31 Dec 26	£ 5,000	-	5,000	-
Total OpEx

Cell Formulas
Range	Formula
I91:K91	I91	=I$6
F102	F102	=ForecastStartDate
I102	I102	=IFS( t_Ex_Services[@Repeats]="All months", t_Ex_Services[@Amount], t_Ex_Services[@Repeats]="Every month in range", IF(AND(t_Ex_Services[@Start]<=I$6,t_Ex_Services[@End]>=I$7),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Odd months", IF(ISODD(MONTH(I$8)),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Even months", IF(ISEVEN(MONTH(I$8)),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="End of quarter", IF(OR(MONTH(I$8)=3,MONTH(I$8)=6,MONTH(I$8)=9,MONTH(I$8)=12),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Repeats annually", IF(MONTH(I$8)=MONTH(t_Ex_Services[@Start]),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Spread over period", IF(AND(t_Ex_Services[@Start]<=I$6,t_Ex_Services[@End]>I$7),I$5*(t_Ex_Services[@Amount]/(t_Ex_Services[@End]-t_Ex_Services[@Start]+1)),IF(AND(t_Ex_Services[@Start]>I$6,t_Ex_Services[@Start]<=I$7),(I$7-t_Ex_Services[@Start]+1)*(t_Ex_Services[@Amount]/(t_Ex_Services[@End]-t_Ex_Services[@Start]+1)),IF(AND(t_Ex_Services[@End]>=I$6,t_Ex_Services[@End]<=I$7),(t_Ex_Services[@End]-I$6+1)*(t_Ex_Services[@Amount]/(t_Ex_Services[@End]-t_Ex_Services[@Start]+1)),0))), t_Ex_Services[@Repeats]="", "")
J102:K102	J102	=IFS( t_Ex_Services[@Repeats]="All months", t_Ex_Services[@Amount], t_Ex_Services[@Repeats]="Every month in range", IF(AND(t_Ex_Services[@Start]<=J$6,t_Ex_Services[@End]>=J$7),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Odd months", IF(ISODD(MONTH(J$8)),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Even months", IF(ISEVEN(MONTH(J$8)),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="End of Quarter", IF(OR(MONTH(J$8)=3,MONTH(J$8)=6,MONTH(J$8)=9,MONTH(J$8)=12),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Repeats annually", IF(MONTH(J$8)=MONTH(t_Ex_Services[@Start]),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Spread over period", IF(AND(t_Ex_Services[@Start]<=J$6,t_Ex_Services[@End]>J$7),J$5*(t_Ex_Services[@Amount]/(t_Ex_Services[@End]-t_Ex_Services[@Start]+1)),IF(AND(t_Ex_Services[@Start]>J$6,t_Ex_Services[@Start]<=J$7),(J$7-t_Ex_Services[@Start]+1)*(t_Ex_Services[@Amount]/(t_Ex_Services[@End]-t_Ex_Services[@Start]+1)),IF(AND(t_Ex_Services[@End]>=J$6,t_Ex_Services[@End]<=J$7),(t_Ex_Services[@End]-J$6+1)*(t_Ex_Services[@Amount]/(t_Ex_Services[@End]-t_Ex_Services[@Start]+1)),0))), t_Ex_Services[@Repeats]="", "")

Named Ranges
Name	Refers To	Cells
ForecastStartDate	=Assumptions!$C$6	F102

How can I adapt this to take account of an annual growth rate (in column D)?

For example, if the user selects, "All months", it would grow like illustrated below:

Forecast Model Example.xlsx
	D	E	F	G	H	I	J	K
92	Annual Growth rate	Repeats	Start	End	Amount
100	5%	All months			£ 1,000	1,004	1,008	1,012
Total OpEx

Cell Formulas
Range	Formula
I100:K100	I100	=H100*(1+t_Ex_Services[@[Annual Growth rate]])^(1/12)

Is there a way to make the formula keep the ability to use the [@Amount] reference rather than use the unstructured reference in the example above?

 

[Alex Blakenburg]

Do you just mean something like this ?

20230506 Compounding increase kcmuppet.xlsx
	D	E	F	G	H	I	J	K
92	Annual Growth rate	Repeats	Start	End	Amount
100	0.05	All months			1000	1,004	1,008	1,013
Sheet1

Cell Formulas
Range	Formula
I100:K100	I100	=(1+t_Ex_Services[@[Annual Growth rate]]/12)^COLUMNS($I100:I100)*t_Ex_Services[@Amount]

 

[kcmuppet]

Hi, thanks for your reply.

No, I'm trying to change the existing formulae in the columns to the right of column H (for each month) so that they take account of/factor in the growth rate in column D.

For example, if in column E they select "Odd months", and the amount is 1000, the current formulae puts 1000 in alternate columns (I, K, and so on), and zero in the other columns.
What I'd like it to do is take account of the % growth in column D. So if column D is 5%, column E is "Odd months" then column I would be 1000, J would be 0, K would be 1008.16, L 0, M 1016.4, etc.

 

[Alex Blakenburg]

I am guessing a bit here but does this help ?

20230506 Compounding increase kcmuppet.xlsx
	D	E	F	G	H	I	J	K	L	M
92	Annual Growth rate	Repeats	Start	End	Amount
99	0.05	All months			1000	1,000	1,004	1,008	1,013	1,017
100	0.05	Odd Months			1000	1,000	0	1,008	0	1,017
Sheet1

Cell Formulas
Range	Formula
I99:M100	I99	=LET(compX,(1+t_Ex_Services[@[Annual Growth rate]]/12)^(COLUMNS($I99:I99)-1)*t_Ex_Services[@Amount], IF(t_Ex_Services[@Repeats]="Odd Months",ISEVEN((COLUMNS($I99:I99)-1))*compX,compX))

 

[kcmuppet]

I'm not sure if you're suggesting adding supplementary rows (which I'd prefer to avoid).

Perhaps it's easier to explain with a clean sheet with the dates rows at the top, and examples of each. In the below, rows 17-22 show what I'd like the results to be in rows 10 to 15 if the values in D10:D15 were all 5%. Ideally, I'd like to achieve that by modifying the formula in I10 so that it can be filled right and down to U15, while keeping the use of the structured references to the table, so that somebody I can work out what is going on with each of the IFS conditions easily

Book4.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O
6					Start	01 May 23	01 Jun 23	01 Jul 23	01 Aug 23	01 Sep 23	01 Oct 23	01 Nov 23
7					End	31 May 23	30 Jun 23	31 Jul 23	31 Aug 23	30 Sep 23	31 Oct 23	30 Nov 23
8						May 23	Jun 23	Jul 23	Aug 23	Sep 23	Oct 23	Nov 23
9	Annual Growth rate	Repeats	Start	End	Amount
10	0%	All months			£ 1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,000
11	0%	Odd months			£ 1,000	1,000	-	1,000	-	1,000	-	1,000
12	0%	End of quarter			£ 1,000	-	1,000	-	-	1,000	-	-
13	0%	Repeats annually	01 May 23		£ 1,000	1,000	-	-	-	-	-	-
14	0%	Spread over period	01 Jul 23	31 Jan 24	£ 1,000	-	-	140	145	140	145	140
15	0%	Every month in range	06 Oct	29 Feb 24	£ 1,000	-	-	-	-	-	-	1,000
16	Desired outcome below (taking account of growth rate in column D
17	5%	All months			£ 1,000	1,000	1,004	1,008	1,012	1,016	1,021	1,025
18	5%	Odd months			£ 1,000	1,000	-	1,008	-	1,016	-	1,025
19	5%	End of quarter			£ 1,000	-	1,004	-	-	1,016	-	-
20	5%	Repeats annually			£ 1,000	1,000	-	-	-	-	-	-
21	5%	Spread over period	01 Jul 23	31 Jan 24	£ 1,000	-	-	141	146.64	142.49	147.83	143.65
22	5%	Every month in range	06 Oct	29 Feb 24	£ 1,000	-	-	-	-	-	-	1,025
Sheet1

Cell Formulas
Range	Formula
I10:O10	I10	=IFS( t_Ex_Services[@Repeats]="All months", t_Ex_Services[@Amount], t_Ex_Services[@Repeats]="Every month in range", IF(AND(t_Ex_Services[@Start]<=I$6,t_Ex_Services[@End]>=I$7),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Odd months", IF(ISODD(MONTH(I$6)),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Even months", IF(ISEVEN(MONTH(I$6)),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="End of quarter", IF(OR(MONTH(I$6)=3,MONTH(I$6)=6,MONTH(I$6)=9,MONTH(I$6)=12),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Repeats annually", IF(MONTH(I$6)=MONTH(t_Ex_Services[@Start]),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Spread over period", t_Ex_Services[@Amount]/(t_Ex_Services[@End]-t_Ex_Services[@Start])*(I$6=MEDIAN(EOMONTH(t_Ex_Services[@Start],-1)+1,EOMONTH(t_Ex_Services[@End],0),I$6))*(MIN(t_Ex_Services[@End],EOMONTH(I$6,0))-MAX(t_Ex_Services[@Start],I$6-1)), t_Ex_Services[@Repeats]="", "")
I11:O15	I11	=IFS( t_Ex_Services[@Repeats]="All months", t_Ex_Services[@Amount], t_Ex_Services[@Repeats]="Every month in range", IF(AND(t_Ex_Services[@Start]<=I$6,t_Ex_Services[@End]>=I$7),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Odd months", IF(ISODD(MONTH(I$8)),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Even months", IF(ISEVEN(MONTH(I$8)),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="End of quarter", IF(OR(MONTH(I$8)=3,MONTH(I$8)=6,MONTH(I$8)=9,MONTH(I$8)=12),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Repeats annually", IF(MONTH(I$8)=MONTH(t_Ex_Services[@Start]),t_Ex_Services[@Amount],0), t_Ex_Services[@Repeats]="Spread over period", t_Ex_Services[@Amount]/(t_Ex_Services[@End]-t_Ex_Services[@Start])*(I$6=MEDIAN(EOMONTH(t_Ex_Services[@Start],-1)+1,EOMONTH(t_Ex_Services[@End],0),I$6))*(MIN(t_Ex_Services[@End],EOMONTH(I$6,0))-MAX(t_Ex_Services[@Start],I$6-1)), t_Ex_Services[@Repeats]="", "")
F13	F13	=ForecastStartDate

That's all the Xl2BB tool will allow me to post, but here's a picture that shows the rest of the existing vs desired results.

 

[Alex Blakenburg]

Assuming your XL2BB references are accurate try this in I10 (columns letter I row 10).
Then copy it down and across.
The basic concept is that your IF statements will return 1 or 0 and multiply that by the value calculation in in compX being the compounding monthly amount.
( I don't think it works as is for the spread over period option)

=LET(compX,(1+t_Ex_Services[@[Annual Growth rate]]/12)^(COLUMNS($I9:I9)-1)*t_Ex_Services[@Amount],
compX*IFS(
t_Ex_Services[@Repeats]="All months",
1,

t_Ex_Services[@Repeats]="Every month in range",
IF(AND(t_Ex_Services[@Start]<=I$6,t_Ex_Services[@End]>=I$7),1,0),

t_Ex_Services[@Repeats]="Odd months",
IF(ISODD(MONTH(I$6)),1,0),

t_Ex_Services[@Repeats]="Even months",
IF(ISEVEN(MONTH(I$6)),1,0),

t_Ex_Services[@Repeats]="End of quarter",
IF(OR(MONTH(I$6)=3,MONTH(I$6)=6,MONTH(I$6)=9,MONTH(I$6)=12),1,0),

t_Ex_Services[@Repeats]="Repeats annually",
IF(MONTH(I$6)=MONTH(t_Ex_Services[@Start]),1,0),

t_Ex_Services[@Repeats]="Spread over period",
1/(t_Ex_Services[@End]-t_Ex_Services[@Start])*(I$6=MEDIAN(EOMONTH(t_Ex_Services[@Start],-1)+1,EOMONTH(t_Ex_Services[@End],0),I$6))*(MIN(t_Ex_Services[@End],EOMONTH(I$6,0))-MAX(t_Ex_Services[@Start],I$6-1)),

t_Ex_Services[@Repeats]="",
0))

 

[kcmuppet]

Thank you - that makes sense. Really appreciate your help

 

[Alex Blakenburg]

No problem. Glad I could help.