Forum Discussion
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>1.5)),”FAIL”,IF(AND(E15=”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”))
If E1 is either Carbon or Alloy and cells F1:K1 value on each cell is either <> as shown above then it needs to say PASS when it is within otherwise say FAIL.
I have been trying all from watching YouTube and other online site but got nowhere so this is my last resort for someone to help me.
Thanks
Adam
Hi
Try the following
=IF(OR(E1="Carbon",E1="God"),"Pass",IF(AND(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>1.5),"Pass","Fail"))
- Hi Gourab,
Thanks for coming back but the formula doesn't work as your first IF statement works as it is always PASS but not the second IF(AND(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>1.5),"Fail","Pass")) statement in which it needs to take <> data to see if its within the given numbers to PASS or FAIL.- Hi
Can you upload the sheet?
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"))
- 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.. :-(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"))
