Forum Discussion

Adam Jalil's avatar
Adam Jalil
Brass Contributor
Apr 30, 2018

Help on Excel IF Condition with AND or OR statement

Good afternoon,   I need help with the following IF statement as it is not working as I want it to.  =IF(AND(E1=”Carbon”,OR((F1<0.5,F1>2.1,G1<0,G1>1.5,H1<0,H1>0.5,I<=0,I1>0.3,J<0,J15>0.25,K1<0,K1>...
Formulas & Functions
Tutorial
SergeiBaklan's avatar
SergeiBaklan
MVP
Apr 30, 2018

Hi Adam,

 

Not sure about your OR logic, if it is correct the formula could be

=IF(AND((E1="Carbon"),OR(F1<0.5,F1>2.1,G1<0,G1>1.5,H1<0,H1>0.5,I1<=0,I1>0.3,J1<0,J1>0.25,K1<0,K1>1.5)),"PASS",   
  IF(AND((E1="Alloy"),OR(F1<0.5,F1>1.5,G1<0,G1>5,H1<0,H1>1,I1<0,I1>1,J1<2,J1>5,K1<11,K1>15)),"PASS",
   "FAIL"))

 

  • Adam Jalil's avatar
    Adam Jalil
    Brass Contributor
    Apr 30, 2018
    Hi Sergei,

    Thank you for coming back on the formula but it doesn't work to what I need it, for example the the data in any of those cells F1-K1 is < or > then it should fail which it is not as I tried numbers greater than those specific and still it say's PASS.. :-(
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Apr 30, 2018

      Like this?

      =IF(AND((E1="Carbon"),NOT(OR(F1<0.5,F1>2.1,G1<0,G1>1.5,H1<0,H1>0.5,I1<=0,I1>0.3,J1<0,J1>0.25,K1<0,K1>1.5))),"PASS",
   IF(AND((E1="Alloy"),NOT(OR(F1<0.5,F1>1.5,G1<0,G1>5,H1<0,H1>1,I1<0,I1>1,J1<2,J1>5,K1<11,K1>15))),"PASS",
   "FAIL"))
    • Adam Jalil's avatar
      Adam Jalil
      Brass Contributor
      Apr 30, 2018
      This formula works on individual cells=IF(AND((E1="Alloy"),OR(F1<0.5,F1>1.5,G1<0,G1>5,H1<0,H1>1,I1<0,I1>1,J1<2,J1>5,K1<11,K1>15)),"FAIL","PASS") but not when I add the Carbon and its cells into the formula
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Apr 30, 2018
        Adam Jalil wrote:
        This formula works on individual cells=IF(AND((E1="Alloy"),OR(F1<0.5,F1>1.5,G1<0,G1>5,H1<0,H1>1,I1<0,I1>1,J1<2,J1>5,K1<11,K1>15)),"FAIL","PASS") but not when I add the Carbon and its cells into the formula

        if continue it when

        =IF(AND((E1="Alloy"),OR(F1<0.5,F1>1.5,G1<0,G1>5,H1<0,H1>1,I1<0,I1>1,J1<2,J1>5,K1<11,K1>15)),"FAIL",IF(AND((E1="Carbon"),OR(F1<0.5,F1>2.1,G1<0,G1>1.5,H1<0,H1>0.5,I1<=0,I1>0.3,J1<0,J1>0.25,K1<0,K1>1.5)),"FAIL","PASS"))

Resources