Finding escalated value (compounded percentage)-CodePudding

I have a table like this:

Year  V1   V2 
2022  101% 102% 
2023  103% 101% 
2024  104% 103%
2025  102% 101%

I want to find escalated value based on start and end years (so an outcome would look like this):

Segment Value StartYear EndYear ValueEscalated
V1      100   2022      2023    104.03 (100*1.01*1.03)  
V2      200   2022      2025    214.34 (200*1.02*1.01*1.03*1.01)

Is it possible to create a formula that would do this?

P.S. Assumption is that values in "Value" column in the outcome are typed in (so they are not formulas).

CodePudding user response：

You could do a lookup on year and segment then use product as follows:

=LET(years,$A$2:$A$5,
segments,$B$1:$C$1,
data,$B$2:$C$5,
seg,A8,
val,B8,
sy,C8,
ey,D8,
col,XLOOKUP(seg,segments,data),
startrow,XLOOKUP(sy,years,col),
endrow,XLOOKUP(ey,years,col),
PRODUCT(val,startrow:endrow))