Indexation Query

[broncos347]

I have a question surrounding indexation which someone can hopefully help me with because I can't get my head around what i need to do.

I have a table which holds an annual index figure, the table has two columns one for the year and one for the indexation rate.

I have another table with a base amount, says £100,000, and this amount has a base year of 2016, the sheet also has another column which shows year of construction, e.g. 2020 (this year could change), what i am trying to do is * the £100,000 to bring it up to a figure in 2020, i can do this by simply *ing the base figure by the indexation rates between 2016 and 2020, for example, £100,000*1*1.023*1.026*1.04*1.025 and this would give me the figure i need, what i can't get my head around is how i can automate this so that if the year of construction changes the £100,000 will be adjusted accordingly.

Any help would be appreciated.

 

[pgc01]

Hi

Try in B5:

=B2*PRODUCT(IF(D2:D16>=B3,IF(D2:D16<=B4,E2:E16)))
This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER

In this example the result is 100,000*1.04*1.052*1.059*1.046*1.036, the factors from 2016 to 2020

Book1
	A	B	C	D	E
1
2	Amount	100000		2010	1.028
3	Start	2016		2011	1.048
4	End	2020		2012	1.034
5				2013	1.027
6	Result	125555.7132		2014	1.047
7				2015	1.049
8				2016	1.040
9				2017	1.052
10				2018	1.059
11				2019	1.046
12				2020	1.036
13				2021	1.022
14				2022	1.024
15				2023	1.031
16				2024	1.030
Sheet1

 

[broncos347]

brilliant works like a dream, i would never have worked that one out, many thanks.

 

[pgc01]

You're welcome. Thanks for the feedback.