IF "Pass","Fail"

**cicihk** · 11-22-2010, 10:52 PM

How do I set the formula if

if F1...I1 <10 = Pass, >=10 Fail (and highlighted red), and any cell between F1....I1 is empty = blank

**canapone** · 11-22-2010, 11:35 PM

Hi, if you have numbers in E1...I1 you could try in K1 (Reading your example)

=IF(E1="","",IF(E1<10,"Pass","Fail"))

To highlight red, in K1 in format/conditional formatting select "Cell value is" "equal to" and write in the last window

="Fail".

Formula in K1 and its condional fomat to be copied to L1:O1

Hope it helps

**cicihk** · 11-22-2010, 11:43 PM

But the problem is "<10" is not a number nor value
if it is equal to 9,8,7,6.....it would be easier to sort out, however it is "<10"

Originally Posted by CANAPONE

Hi, if you have numbers in E1...I1 you could try in K1 (Reading your example)

=IF(E1="","",IF(E1<10,"Pass","Fail"))

To highlight red, in K1 in format/conditional formatting select "Cell value is" "equal to" and write in the last window

="Fail".

Formula in K1 and its condional fomat to be copied to L1:O1

Hope it helps

**canapone** · 11-22-2010, 11:52 PM

Hi, maybe: just an attempt

=IF(F1="","",IF(F1="<10","Pass","Fail"))

In F1 if I've understood you can find a string like "<10": it means Pass, In other case "Fail", if the cell is not empty.

Regards

**cicihk** · 11-23-2010, 12:02 AM

can I set it to? :
=IF(F1="","",IF(F1="<10" or <100000,"Pass","Fail"))

or

tell the formula that if it is not a number (no matter "<1" "<10" "<10000") = Pass ??

because less than 1000000 or "<1" = Pass

Originally Posted by CANAPONE

Hi, maybe: just an attempt

=IF(F1="","",IF(F1="<10","Pass","Fail"))

In F1 if I've understood you can find a string like "<10": it means Pass, In other case "Fail", if the cell is not empty.

Regards

**canapone** · 11-23-2010, 12:20 AM

Hi,

=IF(F1="","",IF(ISNUMBER(F1),"Fail","Pass"))

or

=IF(F1="","",IF(ISERROR(FIND("<",F1)),"Fail","Pass"))

You can also use AND OR to adjust the conditions.

For istance in the there is a "<" in the string but the number is not OK (sorry my English: it's 5 am here) we can adjust the formula excluding some values.

Regards