Forum Discussion

Copper Contributor

Oct 23, 2019

IF / IFS Multiple Options

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 te...

excel

Damien_Rosario

Silver Contributor

Oct 23, 2019

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

Wyn Hopkins

MVP

Oct 23, 2019

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

Damien_Rosario
Silver Contributor
Oct 23, 2019
Wyn Hopkins

I like your example, it is more elegant!

Cheers
Damien