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

Jason Brown

New Member
Joined
May 2, 2018
Messages
11
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
could you give a sample data? I am not sure what you are looking for
 
Upvote 0
StrongAdequateNeeds Imp.None
HighLow/ModModerateMod/HighHigh
Mod/HighLowLow/ModModerateMod/High
ModerateLowLowLow/ModModerate
Low/ModLowLowLowLow/Mod
LowLowLowLowLow

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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")))))))
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,703
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top