Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

    Excel 2016 without the office 365 subscription so I can't use IFS. So I've got two columns of conditions.

    Column 1 has five possible values; low, low/mod, moderate, mod/high, and high.
    Column 2 has four; Strong, adequate, needs improvement, and none exists.

    I want to look at those values and fill in a value in a third column; low, low/mod, moderate, mod/high, and high.

    As I can only nest 7 IF statements I'm guessing I might be unable to do this. But since 10 outcomes are going to be low, four Low/Mod, three Moderate, two Mod/High and one High, is there some way to do it?

    I've been playing around with it but don't want to spend more time. I'm thinking the first IF could use an OR and accomplish the ten low outcomes, then the next IF the four low/mod, etc. Will that work?

    Thanks in advance.

  2. #2
    Board Regular
    Join Date
    Oct 2008
    Posts
    992
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

    could you give a sample data? I am not sure what you are looking for

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

    Strong Adequate Needs Imp. None
    High Low/Mod Moderate Mod/High High
    Mod/High Low Low/Mod Moderate Mod/High
    Moderate Low Low Low/Mod Moderate
    Low/Mod Low Low Low Low/Mod
    Low Low Low Low Low

  4. #4
    New Member
    Join Date
    May 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

    So I've got the left most column and the top row easy enough. I need to cross reference the two to auto fill in the middle stuff.

  5. #5
    Board Regular
    Join Date
    Dec 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

    I guess in order to help we need a better idea of the logic that needs to be considered to get your outcomes. What conditions get these (10 outcomes are going to be low, four Low/Mod, three Moderate, two Mod/High and one High) outcomes?

  6. #6
    New Member
    Join Date
    May 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

    So any time there is a "low" answer the outcome would be low.
    if there is a "Low/Mod" answer the outcome is low unless the second answer is "None exists" and then it is "low/Mod"
    if there is a "moderate" answer the outcome can be Low for Strong and adequate, it can be low/mod for needs improvement, or it can be moderate for none.
    If there is a mod/high answer the outcomes can be low, low/mod, moderate, or mod/high.
    If there is high answer the outcomes can be low/mod, moderate, mod/high, or high.

    This is where I'm at:

    =IF(AND(D4="High",E4="None"),"High",
    IF(AND(or(D4="High",E4="Needs Improvement"),(D4="Mod/High",E4="None")"Moderate",
    IF(AND(D4="High",E4="Needs Improvement"),"Mod/High",
    IF(AND(D4="High",E4="None"),"High",Low))))

    But excel isn't liking what I'm doing, saying it's not a formula and to put an apostrophe in front. I'm not sure if I'm using the OR properly, or what. Obviously it isn't finished as the last response of low needs to further expand the combinations.

  7. #7
    Board Regular
    Join Date
    Dec 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

    Quote Originally Posted by Jason Brown View Post
    So any time there is a "low" answer the outcome would be low.
    if there is a "Low/Mod" answer the outcome is low unless the second answer is "None exists" and then it is "low/Mod"
    if there is a "moderate" answer the outcome can be Low for Strong and adequate, it can be low/mod for needs improvement, or it can be moderate for none.
    If there is a mod/high answer the outcomes can be low, low/mod, moderate, or mod/high.
    If there is high answer the outcomes can be low/mod, moderate, mod/high, or high.

    This is where I'm at:

    =IF(AND(D4="High",E4="None"),"High",
    IF(AND(or(D4="High",E4="Needs Improvement"),(D4="Mod/High",E4="None")"Moderate",
    IF(AND(D4="High",E4="Needs Improvement"),"Mod/High",
    IF(AND(D4="High",E4="None"),"High",Low))))

    But excel isn't liking what I'm doing, saying it's not a formula and to put an apostrophe in front. I'm not sure if I'm using the OR properly, or what. Obviously it isn't finished as the last response of low needs to further expand the combinations.
    Well looking at the table's logic I would do it a different way. I'm not totally sure how you are going to show this data so I'll try to just start with my idea and we can fine tune it from there. What I see from the table's logic is that the scale across the top kind of knocks down the scale on the left. So, I would make a setup where you have column A will hold the value from the column you have shown on the left. And column B has the value from the column across the top. Column A you use 1-4 from Low to High. Column B you would use 0 to 3 from None to Strong. Then you would have a calculation in column C that would be =if(A2-B2<1,1,A2-B2) Then Column D can do a vlookup to get from the value in Column C to the actual phrase you want to use. To do this you will need a simple table that correlates the numbers to the column you have on the left. If those are your only possibilities for this, that should work. If you want to give it a try then we can work through the one off problems.
    Last edited by AntMac; May 16th, 2018 at 02:34 PM.

  8. #8
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,071
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

    You can have up to 64 nested IF() functions; this changed with Excel 2007.

    Your word description doesn't fully describe the outcome for each pairing. Will you please give the proper answer for the combinations listed:

    Low Strong
    Low Adequate
    Low Needsimprovement
    Low None Exists
    Low/Mod Strong
    Low/Mod Adequate
    Low/Mod Needsimprovement
    Low/Mod None Exists
    Moderate Strong
    Moderate Adequate
    Moderate Needsimprovement
    Moderate None Exists
    Mod/High Strong
    Mod/High Adequate
    Mod/High Needsimprovement
    Mod/High None Exists
    High Strong
    High Adequate
    High Needsimprovement
    High None Exists

  9. #9
    Board Regular
    Join Date
    Dec 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

    Quote Originally Posted by thisoldman View Post
    You can have up to 64 nested IF() functions; this changed with Excel 2007.

    Your word description doesn't fully describe the outcome for each pairing. Will you please give the proper answer for the combinations listed:

    Low Strong
    Low Adequate
    Low Needsimprovement
    Low None Exists
    Low/Mod Strong
    Low/Mod Adequate
    Low/Mod Needsimprovement
    Low/Mod None Exists
    Moderate Strong
    Moderate Adequate
    Moderate Needsimprovement
    Moderate None Exists
    Mod/High Strong
    Mod/High Adequate
    Mod/High Needsimprovement
    Mod/High None Exists
    High Strong
    High Adequate
    High Needsimprovement
    High None Exists
    The way I read his response he did like a pivot table of the possiblities. Where the left column and the top column create the middle columns. He can confirm if that's right though.

  10. #10
    New Member
    Join Date
    May 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not sure if nested IF, with AND, in addition to OR, unless there's a better way?

    Didn't realize they upped it to 64. I'll look into that later. What I decided to do was assign numerical values to the various outcomes and multiplied them. So I came up with this formula to examine the values and return the result, but I'm getting an error. What am I doing wrong?


    =IF(XEP4>27,”high”,IF((AND(XEP4>15,XEP4<27),"Mod/High",IF((AND(XEP4>11,XEP4<15),"Moderate",IF((AND(XEP4>6,XEP4<12),"Low/Mod","Low")))))))

Some videos you may like

User Tag List

Tags for this Thread

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
  •