Forum Discussion

ColletteLuffman's avatar
ColletteLuffman
Copper Contributor
Nov 01, 2022

Help please - nested IF AND OR statement

Hi I am struggling with a nested IF AND OR statement, if i write it down in English please can somebody help me with the code please

 

IF Cell M28=3 and Cell Q26 is <2.2 than "Pump1"

IF Cell M28=4 and Cell Q26 is <1.8 than "Pump1" OR

IF Cell M28=4 and Cell Q26 is >1.8 than but < 3.3 "Pump2" OR

IF Cell M28=4 and Cell Q26 is >3.3 than but < 3.7 "Pump3" OR

IF Cell M28=5 and Cell Q26 is <1.3 than "Pump1" OR

IF Cell M28=5 and Cell Q26 is >1.3 than but < 2.8 "Pump2" OR

IF Cell M28=5 and Cell Q26 is >2.8 than but < 3.3 "Pump3" OR

IF Cell M28=6 and Cell Q26 is <0.7 than "Pump1" OR

IF Cell M28=6 and Cell Q26 is >0.7 than but < 2.4 "Pump2" OR

IF Cell M28=6 and Cell Q26 is >2.4 than but < 3.0 "Pump3" Else "NA"

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 01, 2022

    ColletteLuffman 

    =IF(AND(M28=3,Q26<2.2),"Pump1",
    IF(AND(M28=4,Q26<1.8),"Pump1",
    IF(AND(M28=4,Q26>1.8,Q26<3.3),"Pump2",
    IF(AND(M28=4,Q26>3.3,Q26<3.7),"Pump3",
    IF(AND(M28=5,Q26<1.3),"Pump1",
    IF(AND(M28=5,Q26>1.3,Q26<2.8),"Pump2",
    IF(AND(M28=5,Q26>2.8,Q26<3.3),"Pump3",
    IF(AND(M28=6,Q26<0.7),"Pump1",
    IF(AND(M28=6,Q26>0.7,Q26<2.4),"Pump2",
    IF(AND(M28=6,Q26>2.4,Q26<3),"Pump3","NA"))))))))))

     

     

      • dscheikey's avatar
        dscheikey
        Bronze Contributor
        Nov 01, 2022

        ColletteLuffman

        the formula of OliverScheurich  works!

        I like to use IFS() with so many nested IF().

         

         

        =IFS(
AND(M28=3,Q26<2.2),"Pump1",
AND(M28=4,Q26<1.8),"Pump1",
AND(M28=4,Q26>1.8,Q26<3.3), "Pump2",
AND(M28=4,Q26>3.3,Q26<3.7), "Pump3",
AND(M28=5,Q26<1.3),"Pump1",
AND(M28=5,Q26>1.3,Q26<2.8), "Pump2",
AND(M28=5,Q26>2.8,Q26<3.3), "Pump3",
AND(M28=6,Q26<0.7),"Pump1",
AND(M28=6,Q26>0.7,Q26<2.4), "Pump2",
AND(M28=6,Q26>2.4,Q26<3), "Pump3",
TRUE,NA())

         

         

        I would like to draw your attention to the fact that you always use e.g. > 0.7 and < 0.7. You have not caught the case =0.7. Using either a <= or a >= somewhere would resolve this if necessary.

         

        With this formula, that would be done. With option 1 in XLOOKUP one achieves: exact match - If none found, return the next larger item.

         

        =SWITCH(M28,
3,IF(Q26<2.2,"Pump1",NA()),
4,XLOOKUP(Q26,{1.8;3.3;3.7},{"Pump1";"Pump2";"Pump3"},NA(),1),
5,XLOOKUP(Q26,{1.3;2.8;3.3},{"Pump1";"Pump2";"Pump3"},NA(),1),
6,XLOOKUP(Q26,{0.7;2.4;3},{"Pump1";"Pump2";"Pump3"},NA(),1),
NA())

         

Resources