How to calculate This year's value of historic data using annual Inflation rates

[highlystrung]

Hi, I'm trying to calculate the value today of some historic expense data on Food using UK inflation data. I've created a small sample file of what I'm trying to do in which I've done the calculations in the column "Equivalent Cost 2012" manually.My approach is quite laborious and I'm sure there is a much neater way to do it using inbuilt EXCEL formulas and / or arrays, - but a search through the forums and a look through my EXCEL formula book hasn't given me much of a lead.

I want to apply the principal to a larger set of data and find a neat way to update it annually i.e. update the data in 2013 to show the value in 2013 so the solution should be able to grow each year.

Can anybody point me in the right direction please?

thanks,

NeilInfation example file.xlsm

[Ace_XL]

In d2 use this array formula

=B2*PRODUCT(1+C2:$C$11)

Copy down. Remeber to Ctrl+Shift+Enter

[highlystrung]

Hi thanks for input but it gives a figure of £157 in 2003 when my manual calculation comes to £197. So it doesn't seem to work....? Neil

[Ace_XL]

Remember to Ctrl+Shift+Enter

Maybe you forgot that

Some reading on array formulas too
www.cpearson.com/excel/arrayformulas.aspx

[highlystrung]

Hi, yes that was the issue. It's clear I need to read up on this a bit more. Thanks for your help, regards, Neil