Help with IF statement on cells that could contain text or numbers

[Ross Mac]

I need some help, I'm a basic user of Excel and have spent hours trying to work this out. I have data where I need to look at three cells and if any of them are greater than 100 I need to deliver a "Fail", otherwise a "Pass". The problem I am having is that in all cases either one or two of the cells has text in, "N/A" the formula therefore always returns a "Fail".

=IF(OR(T2>100,V2>100,X2>100),"FAIL","PASS")

 

[Somendra Misra]

Hi Ross,

Welcome to chandoo forum.

Try below formula
=IF(COUNTIF(T2:X2,">100"),"FAIL","PASS")

Regards,

 

[Khalid NGO]

Hi Ross,
Welcome to the forum

Your formula sees to be work, you just need to change the cell format to:
#;-#;"N/A";

and instead of inserting N/A in your cell try inserting 0.
above custom formatting will display 0 as N/A
and your formula will work same.
Regards,

 

[Mike H..]

I need some help, I'm a basic user of Excel and have spent hours trying to work this out. I have data where I need to look at three cells and if any of them are greater than 100 I need to deliver a "Fail", otherwise a "Pass". The problem I am having is that in all cases either one or two of the cells has text in, "N/A" the formula therefore always returns a "Fail".

=IF(OR(T2>100,V2>100,X2>100),"FAIL","PASS")

Hi,

If for whatever reason you cannot include the full range of T to X in your formula then you could do this

=IF(OR(AND(ISNUMBER(T2),T2>100),FALSE,AND(ISNUMBER(X2),X2>100)),"FAIL","PASS")

Me, I wouldn't put NA in the range.

 

[Ross Mac]

=IF(OR(AND(ISNUMBER(T2),T2>100),FALSE,AND(ISNUMBER(X2),X2>100),FALSE,AND(ISNUMBER(V2),V2>100)),"FAIL","PASS")

That does the trick, many thanks for all your help!

 

[Mike H..]

=IF(OR(AND(ISNUMBER(T2),T2>100),FALSE,AND(ISNUMBER(X2),X2>100),FALSE,AND(ISNUMBER(V2),V2>100)),"FAIL","PASS")

That does the trick, many thanks for all your help!

Hi,

I don't know how that FALSE got into the middle of the formula, typo I guess. It does no harm but it's not necessary.

=IF(OR(AND(ISNUMBER(T2),T2>100),AND(ISNUMBER(X2),X2>100),AND(ISNUMBER(V2),V2>100)),"FAIL","PASS")