# CPI Indexing- Formula

• Hi all

Well this is a tricky one I have a attached a before and after shot of what i'm trying to achieve.
The formula is for Column I- it needs to take the date purchased and find what CPI index was valid for that period which is called the Purcahse Date CPI. Then the
(Original purchase cost * the purcahse Date CPI) divided by The last CPI figure 123.(this last figure never changes.

If the date is pre 1/7/85 it needs to write PRE CGT
If the date is after 1/10/99 then it just puts the Original Purchase cost.

Also the Sheet 1 needs to be hidden so it doesnt show up/ or can't be accessed by anyone but myself.

Hope thats clear.

Stacy

## Files

• Re: CPI Indexing- Formula

Stacy,

This was the formula that I used in range I11

=IF(D11<DATEVALUE("1/7/1985"),"PRE CGT",(H11*Sheet1!D19)/Sheet1!D21)

Let me know!

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Re: CPI Indexing- Formula

Yes that worked for that line but the thing is these figures are purely IF so they will be changing i need to formula to cover all senarios.

• Re: CPI Indexing- Formula

So you're using CPI figures to discount the value of the goods to today's terms?

A quick solution would be to look the purchase date up in a single column look up table. You'll need to ensure that the date (including the day and month ...so you can span quarters) is formatted (as a date and not just text) in both the source column and in the look up table.

Try looking up examples on Lookup, Vlookup and Hlookup (good examples on this site).

hope this helps.

Robert Hind
Perth - Western Australia

• Re: CPI Indexing- Formula

Can't you accountants let the past be history. We all know that after awhile our money becomes worthless :facepull:

Ok so let me see if I get this correct.

=IF(D11<DATEVALUE("1/7/1985"),"PRE CGT",(H11*Sheet1!D19)/Sheet1!D21)

Based on the date in the D Column I need to get the CPI that is indicated for that period??? So the Sheet1!D19 needs to be variable.

And the Sheet1!D21 portion needs to get the latest CPI and it will also need to be variable?

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Re: CPI Indexing- Formula

Try the attached ...it should point you in the right direction.

Note the use of a dynamic named range called "CPI_Table" (this will range will grow as entries are added to the bottom.

See on the main sheet how the purchase date is looked up.

## Files

Robert Hind
Perth - Western Australia

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!