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

[Jason Brown]

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.

 

[snjpverma]

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

 

[Jason Brown]

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Strong[/TD]
[TD]Adequate[/TD]
[TD]Needs Imp.[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]Low/Mod[/TD]
[TD]Moderate[/TD]
[TD]Mod/High[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD]Mod/High[/TD]
[TD]Low[/TD]
[TD]Low/Mod[/TD]
[TD]Moderate[/TD]
[TD]Mod/High[/TD]
[/TR]
[TR]
[TD]Moderate[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low/Mod[/TD]
[TD]Moderate[/TD]
[/TR]
[TR]
[TD]Low/Mod[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low/Mod[/TD]
[/TR]
[TR]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jason Brown]

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]

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]

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]

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.

 

[thisoldman]

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:

[TABLE="class:grid"]
<tbody>[TR]
[TD]Low[/TD]
[TD]Strong[/TD]
[/TR]
[TR]
[TD]Low[/TD]
[TD]Adequate[/TD]
[/TR]
[TR]
[TD]Low[/TD]
[TD]Needsimprovement[/TD]
[/TR]
[TR]
[TD]Low[/TD]
[TD]None Exists[/TD]
[/TR]
[TR]
[TD]Low/Mod[/TD]
[TD]Strong[/TD]
[/TR]
[TR]
[TD]Low/Mod[/TD]
[TD]Adequate[/TD]
[/TR]
[TR]
[TD]Low/Mod[/TD]
[TD]Needsimprovement[/TD]
[/TR]
[TR]
[TD]Low/Mod[/TD]
[TD]None Exists[/TD]
[/TR]
[TR]
[TD]Moderate[/TD]
[TD]Strong[/TD]
[/TR]
[TR]
[TD]Moderate[/TD]
[TD]Adequate[/TD]
[/TR]
[TR]
[TD]Moderate[/TD]
[TD]Needsimprovement[/TD]
[/TR]
[TR]
[TD]Moderate[/TD]
[TD]None Exists[/TD]
[/TR]
[TR]
[TD]Mod/High[/TD]
[TD]Strong[/TD]
[/TR]
[TR]
[TD]Mod/High[/TD]
[TD]Adequate[/TD]
[/TR]
[TR]
[TD]Mod/High[/TD]
[TD]Needsimprovement[/TD]
[/TR]
[TR]
[TD]Mod/High[/TD]
[TD]None Exists[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]Strong[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]Adequate[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]Needsimprovement[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]None Exists[/TD]
[/TR]
</tbody>[/TABLE]

 

[AntMac]

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:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Low[/TD]
[TD]Strong[/TD]
[/TR]
[TR]
[TD]Low[/TD]
[TD]Adequate[/TD]
[/TR]
[TR]
[TD]Low[/TD]
[TD]Needsimprovement[/TD]
[/TR]
[TR]
[TD]Low[/TD]
[TD]None Exists[/TD]
[/TR]
[TR]
[TD]Low/Mod[/TD]
[TD]Strong[/TD]
[/TR]
[TR]
[TD]Low/Mod[/TD]
[TD]Adequate[/TD]
[/TR]
[TR]
[TD]Low/Mod[/TD]
[TD]Needsimprovement[/TD]
[/TR]
[TR]
[TD]Low/Mod[/TD]
[TD]None Exists[/TD]
[/TR]
[TR]
[TD]Moderate[/TD]
[TD]Strong[/TD]
[/TR]
[TR]
[TD]Moderate[/TD]
[TD]Adequate[/TD]
[/TR]
[TR]
[TD]Moderate[/TD]
[TD]Needsimprovement[/TD]
[/TR]
[TR]
[TD]Moderate[/TD]
[TD]None Exists[/TD]
[/TR]
[TR]
[TD]Mod/High[/TD]
[TD]Strong[/TD]
[/TR]
[TR]
[TD]Mod/High[/TD]
[TD]Adequate[/TD]
[/TR]
[TR]
[TD]Mod/High[/TD]
[TD]Needsimprovement[/TD]
[/TR]
[TR]
[TD]Mod/High[/TD]
[TD]None Exists[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]Strong[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]Adequate[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]Needsimprovement[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]None Exists[/TD]
[/TR]
</tbody>[/TABLE]

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]

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")))))))