Help on Excel IF Condition with AND or OR statement

[Adam Jalil]

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

[#2]

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

[#3]

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

[#4]

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.

[#5]

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.. :(

[#6]

Hi
Can you upload the sheet?

[#7]

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

[#8]

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

[#9]

---

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

[#10]

Hi Gourab,

I have uploaded the sheet and what I want is the data from E15 to K15 section which is not working on the following formula

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

[#11]

Hi Sergei,

Thanks for the formulas but whatevery I try doesn't seem to work when I have cells like E15 onwards. I have attached the text excel document on the cells which I want and you can also see the formulas which you have given works on cells E1 onward on F3 and F6.

What I want is the data I have on cells E15 to K15 to work on those formulas...

Thanks a million 

[#12]

Adam, are you sure your F1:K1 are not % but decimal numbers? With per cents it works if

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

[#13]

Hi Sergei, Thank you as it works now on what I want :)

[#14]

Hi Adam Jalil

Sorry but I cant make out your criteria ie F1<0.5,F1>1.5 here the value could be anything . Please clear the issue.

[#15]

Hi Gourab, The values were in %... I have it working now so thank you for assisting me on this matter.

Regards

Adam