Results 1 to 5 of 5

Thread: Formula help

  1. #1

    Formula help

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm building an investment appraisal model and have lines that look up benefits and costs

    IF($G12="",0,IF(AND($H12="ongoing benefit",AA$8>=$G12,($AA$8,$I12),AA$8<$FE12),$FF12,0)+IF(AND($H12<>"ongoing benefit",AA$8>=$G12,AA$8<=$FE12),$FF12,0))

    G12= start month
    H12= ongoing benefit/costs or length in months
    AA8= month on the top of the column
    I12= any delay in months
    FE= formula that calculates the end month
    FF= formula that calculates the monthly amount

    Now I would like to introduce inflation to this formula. Normally I would just add from year 1 [above formula]*(1+[rate] and ^2 on year 2 etc. but in this instance I do not have a single inflation rate but a table with 6 different countries and a projected different rate for each year. I can set up a column to choose a country but I'm not sure how to build a look up from the table to the formula, or if I should build a separate line after each cost/benefit line just look up the inflation rate. I want to keep it simple but I'm lacking ideas.. any help would be appreciated

  2. #2
    Post an example workbook so we can see what the inflation rate table looks like and the core data.

  3. #3
    Thanks, attached (only two years of data due to the attachment size restrictions). 5b is meant to be for all costs and benefits, hence I thought that I could choose the country in column K and use that as a point of reference for the inflation rate (if the country in question is not one of the 6 countries, default is US rate). So I'm looking for something along the lines "if country in column K is US then give me US rate, if country in column K is Australia then give me Australia rate etc.". The challenge is that building it into the formulas starting from column AA will make the formula really long, but building it into a separate column will create problems because the rate is different every year. Is my best bet to create a different lookup column for each year with a nested lookup formula for multiple countries or would there be a more integrated approach?
    Attached Files Attached Files

  4. #4
    I meant starting from column AD..

  5. #5
    I have sorted out this problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts