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.

 

[Jason Brown]

It's not a pivot table, but rather we're looking at various factors and assigning a risk level and a control level and rather than have to manually go through the list and enter the residual risk, I wanted to have a formula automatically enter the result. I'm using conditional formatting to then colorize the results.

 

[Jason Brown]

I get the NAME error when I do this:

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

 

[Jason Brown]

N/M saw that I had left out the 4 in the first XEP as I was copying it. Thanks to all for their help.

 

[markmzz]

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.

Maybe this in C2 and copy down

=INDEX({"Low/Mod","Moderate","Mod/High","High";"Low","Low/Mod","Moderate","Mod/High";"Low","Low","Low/Mod","Moderate";"Low","Low","Low","Low/Mod";"Low","Low","Low","Low"},
MATCH(A2,{"High";"Mod/High";"Moderate";"Low/Mod";"Low"},0),MATCH(B2,{"Strong","Adequate","Needs Imp.","None"},0))

Markmzz

 

[AntMac]

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

If you are ok with using the numerical values try my solution I gave earlier that should make it much easier.

 

[markmzz]

Hi!

You can try this too:

1) Create a table in G1:K6 with the layout below.

2) Create the name TabData to the range $G$1:$K$6.

3) After that, use the formula below in C2 and copy down.

=INDEX(TabData,MATCH(A2,INDEX(TabData,,1),0),MATCH(B2,INDEX(TabData,1,),0))


[TABLE="class: grid, width: 728"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Column01[/TD]
[TD]Column02[/TD]
[TD]Column03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Strong[/TD]
[TD]Adequate[/TD]
[TD]Needs Imp.[/TD]
[TD]None[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]High[/TD]
[TD]Needs Imp.[/TD]
[TD]Mod/High[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]High[/TD]
[TD]Low/Mod[/TD]
[TD]Moderate[/TD]
[TD]Mod/High[/TD]
[TD]High[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]High[/TD]
[TD]Strong[/TD]
[TD]Low/Mod[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mod/High[/TD]
[TD]Low[/TD]
[TD]Low/Mod[/TD]
[TD]Moderate[/TD]
[TD]Mod/High[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Moderate[/TD]
[TD]None[/TD]
[TD]Moderate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Moderate[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low/Mod[/TD]
[TD]Moderate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Moderate[/TD]
[TD]Adequate[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Low/Mod[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low/Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]High[/TD]
[TD]None[/TD]
[TD]High[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Moderate[/TD]
[TD]Adequate[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]High[/TD]
[TD]None[/TD]
[TD]High[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Low[/TD]
[TD]Needs Imp.[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Low[/TD]
[TD]Adequate[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]High[/TD]
[TD]Needs Imp.[/TD]
[TD]Mod/High[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Moderate[/TD]
[TD]Needs Imp.[/TD]
[TD]Low/Mod[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Moderate[/TD]
[TD]Strong[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Mod/High[/TD]
[TD]Strong[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Mod/High[/TD]
[TD]Strong[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Moderate[/TD]
[TD]Adequate[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Low/Mod[/TD]
[TD]None[/TD]
[TD]Low/Mod[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Mod/High[/TD]
[TD]None[/TD]
[TD]Mod/High[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Mod/High[/TD]
[TD]Needs Imp.[/TD]
[TD]Moderate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Low[/TD]
[TD]None[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Low[/TD]
[TD]Adequate[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**********[/TD]
[TD]***********[/TD]
[TD]*********[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**********[/TD]
[TD]*********[/TD]
[TD]**********[/TD]
[TD]***********[/TD]
[TD]**********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz