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

A

B

C

D

E

F

G

H

I

J

K

L

1

Column01

Column02

Column03

Strong

Adequate

Needs Imp.

None

2

High

Needs Imp.

Mod/High

High

Low/Mod

Moderate

Mod/High

High

3

High

Strong

Low/Mod

Mod/High

Low

Low/Mod

Moderate

Mod/High

4

Moderate

None

Moderate

Moderate

Low

Low

Low/Mod

Moderate

5

Moderate

Adequate

Low

Low/Mod

Low

Low

Low

Low/Mod

6

High

None

High

Low

Low

Low

Low

Low

7

Moderate

Adequate

Low

8

High

None

High

9

Low

Needs Imp.

Low

10

Low

Adequate

Low

11

High

Needs Imp.

Mod/High

12

Moderate

Needs Imp.

Low/Mod

13

Moderate

Strong

Low

14

Mod/High

Strong

Low

15

Mod/High

Strong

Low

16

Moderate

Adequate

Low

17

Low/Mod

None

Low/Mod

18

Mod/High

None

Mod/High

19

Mod/High

Needs Imp.

Moderate

20

Low

None

Low

21

Low

Adequate

Low

22

***

**********

***********

*********

**

**

**

**********

*********

**********

***********

**********

**

<tbody>
</tbody>

Markmzz