Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: nested formulas

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Columbus, OH
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to join a series of nested formulas and don't know if that is possible. Of course I have hit the limit and need to join 2 sets of nested formulas if possible. If someone would have any ideas please let me know. What I am doing is checking data in 2 cells and then computing a percentage based on the data inputed. This is my work in progress so far...
    =IF(AND(X16=1,Y16="G"),0.045,IF(AND(X16=1,Y16="VG"),0.055,IF(AND(X16=1,Y16="E"),0.065,IF(AND(X16=2,Y16="G"),0.035,IF(AND(X16=2,Y16="VG"),0.045,IF(AND(X16=2,Y16="E"),0.055,IF(OR(Y16="RI",Y16="U"),0))))))))

    I need to join the additional criteria with the difference being X16 becomes equal to 3 and 4 and goes through the same Y criteria.

    Perhaps there is an easier way other than if statements???


    [ This Message was edited by: wgreen on 2002-02-19 05:59 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, it's really hard to answer with something specific without the "specifics".

    In one word, YES, it is possible.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-19 05:27, wgreen wrote:
    I am trying to join a series of nested formulas and don't know if that is possible. Of course I have hit the limit and need to join 2 sets of nested formulas if possible. If someone would have any ideas please let me know. What I am doing is checking data in 2 cells and then computing a percentage based on the data inputed. This is my work in progress so far...
    =IF(AND(X16=1,Y16="G"),0.045,IF(AND(X16=1,Y16="VG"),0.055,IF(AND(X16=1,Y16="E"),0.065,IF(AND(X16=2,Y16="G"),0.035,IF(AND(X16=2,Y16="VG"),0.045,IF(AND(X16=2,Y16="E"),0.055,IF(OR(Y16="RI",Y16="U"),0))))))))

    I need to join the additional criteria with the difference being X16 becomes equal to 3 and 4 and goes through the same Y criteria.

    Perhaps there is an easier way other than if statements???


    [ This Message was edited by: wgreen on 2002-02-19 05:59 ]
    Make a 3-column list of the values of interest (which your IF formula is referring to) in some unused range, say, in F1:H6 like the one that follows:

    {1,"G",0.045;
    1,"VG",0.055;
    1,"E",0.065;
    2,"G",0.035;
    2,"VG",0.045;
    2,"E",0.055}

    Use the following formula (instead of a long IF formula) to retrieve the value of interest from the above list, given the values in X16 and Y16:

    =INDEX(H1:H6,SUMPRODUCT(MATCH(X16&"@"&Y16,F1:F6&"@"&G1:G6,0)))

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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