Formula that calculates using Inflation factors

[gheyman]

I have a commodity code in column A. then in Columns B through M I have inflation factors for each year starting in 2016 (B has an inflation factor of 2.177%, C 2017=3.419%, D 2018=.03360, E 2019=.02415, F 2020=.02701, G 2021=.0241,H 2022=.02860 and so on (Row 1 has the year Row two has the factor and A2 is where the commodity code is

(On my sheet I have commodity codes going down column A and the Factors going down the other columns as well)

On a separate sheet I have a field where I enter a commodity code in A1 Then A2 I Enter the starting year and A3 the End Year

What I need the formula to do is to look on sheet2 find the matching commodity code. Then depending on the start end year, I year it to figure out the total inflation

So if my start year was 2017 and my end year was 2019 Then it should use the 2018 and 2019 rates and result in 1.05856
=(1+(1*3.360))*(1+2.415)

I know you can do a Sumproduct but that means manually changing the formula because my start and end years change. I need something dynamic. for example someone could enter a start date of 2016 and end date of 2022

 

[AlanY]

this solution make use of helpers in Row 5

Book1
	A	B	C	D	E	F	G	H
1		2016	2017	2018	2019	2020	2021	2022
2	Code 1	2.177%	3.419%	3.360%	2.415%	2.701%	2.410%	2.860%
3
4
5		1	1	1.0336	1.02415	1	1	1
6
7	Code 1	2017	2019	1.058561
Sheet4

Cell Formulas
Range	Formula
B5	=IF(AND(B1>$B$7,B1<=$C$7),1+B2,1)
D7	=PRODUCT(B5:H5)

 

[kvsrinivasamurthy]

Data in Sheet1

Code 1

<tbody>
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[TH="align: center"]H[/TH]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"]2021[/TD]
[TD="align: right"]2022[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2.177%[/TD]
[TD="align: right"]3.419%[/TD]
[TD="align: right"]3.360%[/TD]
[TD="align: right"]2.415%[/TD]
[TD="align: right"]2.701%[/TD]
[TD="align: right"]2.410%[/TD]
[TD="align: right"]2.860%[/TD]

</tbody>

Sheet2
A1=Code1
A2=2017
A3=2019
ARRAY Formula in A4

=PRODUCT((1+SUMIF(OFFSET(INDEX(Sheet1!$B$2:$H$3,MATCH($A$1,Sheet1!$A$2:$A$3,0),MATCH($A$2+1,Sheet1!$B$1:$H$1,0)),0,ROW(INDIRECT($A$2&":"&$A$3-1))-$A$2),">0")))

How ARRAY formula is entered


Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.

 

[kvsrinivasamurthy]

In post# 3 Data is only in Row2. In Formula I have added Row 3 also.
In Row 3 You can add data for Code2.
If more rows required,
Then Row 4 for Code3.
So on..