CPI Frequency Flag - Formula Simplication

[Superhick]

Hi there,

I want to simplify an excessively long nested IF(OR formula; can anyone help?

Here is the formula:
=IF(OR(I$6=$F15+$H15,I$6=$F15+$H15*2,I$6=$F15+$H15*3,I$6=$F15+$H15*4,I$6=$F15+$H15*5,I$6=$F15+$H15*6,I$6=$F15+$H15*7,I$6=$F15+$H15*8,I$6=$F15+$H15*9,I$6=$F15+$H15*10),1,0)

I have attached a screenshot of what I'm trying to do. I am building a rent forecast model, and want to apply inflation. However, inflation could be every 2 years, or every 3 years etc. as specified. My formula only applies CPI after the start year, and then applies CPI at the frequency specified. However, I would need 20 or more OR formulas for my formula to forecast out 20+ years, but I'm sure there is a more graceful and compact formula to do the job. I'm just not sure how to do it.

Appreciative of anyone's help. Thanks very much

 

[StephenCrump]

Welcome to the Forum!

Perhaps something like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1
2
3
4
5
6									2015	2016	2017	2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	2033	2034
7
8
9
10						CPI	2.0%
11
12
13
14						Start Year	Amount	Frequency
15						2018	1,000.00	1				1,000.00	1,020.00	1,040.40	1,061.21	1,082.43	1,104.08	1,126.16	1,148.69	1,171.66	1,195.09	1,218.99	1,243.37	1,268.24	1,293.61	1,319.48	1,345.87	1,372.79
16						2017	2,000.00	1			2,000.00	2,040.00	2,080.80	2,122.42	2,164.86	2,208.16	2,252.32	2,297.37	2,343.32	2,390.19	2,437.99	2,486.75	2,536.48	2,587.21	2,638.96	2,691.74	2,745.57	2,800.48
17						2017	1,000.00	1			1,000.00	1,020.00	1,040.40	1,061.21	1,082.43	1,104.08	1,126.16	1,148.69	1,171.66	1,195.09	1,218.99	1,243.37	1,268.24	1,293.61	1,319.48	1,345.87	1,372.79	1,400.24
18						2016	2,000.00	2		2,000.00	2,000.00	2,040.00	2,040.00	2,080.80	2,080.80	2,122.42	2,122.42	2,164.86	2,164.86	2,208.16	2,208.16	2,252.32	2,252.32	2,297.37	2,297.37	2,343.32	2,343.32	2,390.19
19						2016	1,000.00	2		1,000.00	1,000.00	1,020.00	1,020.00	1,040.40	1,040.40	1,061.21	1,061.21	1,082.43	1,082.43	1,104.08	1,104.08	1,126.16	1,126.16	1,148.69	1,148.69	1,171.66	1,171.66	1,195.09
20						2025	2,000.00	3											2,000.00	2,000.00	2,000.00	2,040.00	2,040.00	2,040.00	2,080.80	2,080.80	2,080.80	2,122.42
21						2018	1,000.00	5				1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,020.00	1,020.00	1,020.00	1,020.00	1,020.00	1,040.40	1,040.40	1,040.40	1,040.40	1,040.40	1,061.21	1,061.21
22						2021	2,000.00	5							2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,040.00	2,040.00	2,040.00	2,040.00	2,040.00	2,080.80	2,080.80	2,080.80	2,080.80
23						2017	1,000.00	7			1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,020.00	1,020.00	1,020.00	1,020.00	1,020.00	1,020.00	1,020.00	1,040.40	1,040.40	1,040.40	1,040.40
24						2017	2,000.00	8			2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,040.00	2,040.00	2,040.00	2,040.00	2,040.00	2,040.00	2,040.00	2,040.00	2,080.80	2,080.80
Sheet1

Cell Formulas
Range	Formula
I15:AB24	I15	=IF(I$6>=$F15,$G15*(1+CPI)^INT((I$6-$F15)/$H15),"")

Named Ranges
Name	Refers To	Cells
CPI	=Sheet1!$G$10	I15:AB24

Or for your original question:
I15: =IF(AND(I$6>$F15,MOD(I$6-$F15,$H15)=0),1,"")

 

[Superhick]

That is perfectly elegant and what I was hoping for, thank you very much Stephen!