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

Jason Brown

New Member
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.
 

Jason Brown

New Member
StrongAdequateNeeds Imp.None
HighLow/ModModerateMod/HighHigh
Mod/HighLowLow/ModModerateMod/High
ModerateLowLowLow/ModModerate
Low/ModLowLowLowLow/Mod
LowLowLowLowLow

<tbody>
</tbody>
 

Jason Brown

New Member
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.
 

AntMac

Board Regular
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?
 

Jason Brown

New Member
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.
 

AntMac

Board Regular
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:

thisoldman

Well-known Member
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:

LowStrong
LowAdequate
LowNeedsimprovement
LowNone Exists
Low/ModStrong
Low/ModAdequate
Low/ModNeedsimprovement
Low/ModNone Exists
ModerateStrong
ModerateAdequate
ModerateNeedsimprovement
ModerateNone Exists
Mod/HighStrong
Mod/HighAdequate
Mod/HighNeedsimprovement
Mod/HighNone Exists
HighStrong
HighAdequate
HighNeedsimprovement
HighNone Exists

<tbody>
</tbody>
 

AntMac

Board Regular
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:

LowStrong
LowAdequate
LowNeedsimprovement
LowNone Exists
Low/ModStrong
Low/ModAdequate
Low/ModNeedsimprovement
Low/ModNone Exists
ModerateStrong
ModerateAdequate
ModerateNeedsimprovement
ModerateNone Exists
Mod/HighStrong
Mod/HighAdequate
Mod/HighNeedsimprovement
Mod/HighNone Exists
HighStrong
HighAdequate
HighNeedsimprovement
HighNone Exists

<tbody>
</tbody>
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.
 

Jason Brown

New Member
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

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top