Forum Discussion

taylordanrw's avatar
taylordanrw
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 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

 

 

excel

3 Replies

  • Damien_Rosario's avatar
    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's avatar
      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")

       

Resources