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
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    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