IF / IFS Multiple Options

[taylordanrw]

Hello,

So I'm trying to create a BCG classifying matrix with custom data, and I've hit the wall trying to build a nested IF / IFS function (if this is the right way) so that if the below logic tests are met the value presents:

If E1, G1 & N1 are >=85 = Value A

If E1 & N1 >=75 <=84 & G1 <=40 = Value B

If E1, G1 & N1 are <=40 = Value C

If none of the above met = Value D

I've been banging my head against walls for ages with this, and not even sure it is possible anymore so any help greatly appreciated.

Dan

[#2]

Hi @taylordanrw 

Maybe try this?

=IF(AND(E1>=85,G1>=85,N1>=85),"Value A",IF(AND(OR(AND(E1>=75,E1<=84),AND(N1>=75,N1<=84)),G1<=40),"Value B",IF(AND(E1<=40,G1<=40,N1<=40),"Value C","Value D")))

I find it easier to break the conditions down into individual IF statements first to test out the formula and then I combine it later.

See how you go? Appeared to work for me in testing.

Cheers & best wishes

Damien

[#3]

Hi All

I like Damien's approach and I would strongly recommend leaving the formula split apart into multiple helper columns.  Makes it easier for someone picking up the file to understand and debug

Only combine into a mega formula if you really have to.

Another mega formula approach would be this if you're on O365.    I'd stick with the multiple column IF statement approach if I had a choice,  but thought I'd share this SWITCH TRUE technique.  Also sometimes using MAX and MIN can simplify complex IF statements

=SWITCH(
TRUE(),
MIN(E1,G1,N1)>=85,"A",
MAX(E1,G1,N1)<=40,"C",
AND(G1<=40,MIN(E1,N1)>=75,MAX(E1,N1)<=84),"B",
"D")

[#4]

@Wyn Hopkins

I like your example, it is more elegant!

Cheers

Damien