+ Reply to Thread
Results 1 to 5 of 5

different equations based on if statement

  1. #1
    Registered User
    Join Date
    03-23-2012
    Location
    Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    5

    different equations based on if statement

    I have a spreadsheet calculating expected returns on certain investments. There is a different equation to calculate the return for each different type of equation. i want to build an equation like

    "=if(a1="IRA",'IRA schedule equation'!A1,if(A1="Roth IRA",'Roth IRA Schedule'!A1),"Error"))

    Where A1 in the respective sheet would hold the calculating equation. I tried this, and it doesn't behave as though the equation is in the current sheet cell. Is there a different way I can implement this?

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: different equations based on if statement

    Hi Tom,

    Normally you'd just put the equation into the nested IF function, e.g.

    =IF(A1="IRA",VLOOKUP(B10,D1:F20,3,0),IF(A1="Roth IRA",INDEX(B1:B15,MATCH(G4,C2:E28,0)),"Error"))

    When you reference another cell within a formula, you're referencing that cell's value, not re-evaluating the formula it contains. You could create UDF's for the various functions and call them within the nested IF's, or I believe you may also be able to use Defined Names to perform some calculations.

  3. #3
    Registered User
    Join Date
    03-23-2012
    Location
    Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: different equations based on if statement

    Thanks for the advice. I wanted to avoid including the equations in the nested IFs for the sake of clarity, because the full range is 26 different class codes, which correspond to one of six equations.

    I will look into UDFs and named ranges.

  4. #4
    Registered User
    Join Date
    03-23-2012
    Location
    Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: different equations based on if statement

    I ended up finding a simpler, more elegant solution.

    I simply made a table on sheet 2 that will calculate all the possible answers for each combination, then the "home" sheet uses an hlookup to pull out the proper answer from the table of all answers.

    Thanks again for the help and steering me in the right direction!

  5. #5
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: different equations based on if statement

    Thanks Tom. Glad you were able to sort it out, and that I could assist in any way.

    Have a good day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1