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

    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!

    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.



    Thanks for your help.

    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?

Participate now!

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

Register Yourself Login