IF_true/false

[cicihk]

Can anyone help on this formula? I would appreciate!!

[Azam Ali]

Hi cicihk

copy the following in cell # R11

=IFERROR(IF(MATCH("Fail",K11:P11,0)>0,"Fail",""),"")

[cicihk]

Hi cicihk

copy the following in cell # R11

=IFERROR(IF(MATCH("Fail",K11:P11,0)>0,"Fail",""),"")

It works. You're superb!!
Can you tell me ">0" means?

[zbor]

That formula will work only for XL 2003.

This will work for previous versions too:

=IF(COUNTIF(K4:P4, "Fail")>0, "Fail", "")

[Azam Ali]

hi cicihk

first the match function is giving the column numbre if Fail is found in the range. that number >0 will give result true if there is a fail in the range.

an if true if function give result "Fail"

[cicihk]

That formula will work only for XL 2003.

This will work for previous versions too:

=IF(COUNTIF(K4:P4, "Fail")>0, "Fail", "")

It works as well. Thanks. But why you guys put ">0" ?

[cicihk]

hi cicihk

first the match function is giving the column numbre if Fail is found in the range. that number >0 will give result true if there is a fail in the range.

an if true if function give result "Fail"

Azam Ali,
ic. will it be ok if I omit ">0" in the function?

[Azam Ali]

No. dont omit >0; becouse it is checking the existance of "Fail" in the range

break the function and evaluate. this may help in understanding. e.g

in one cell say S11 first look at the result of =MATCH("Fail",K11:P11,0)
then in next cell T11 look at =IF(S11>0,"Fail","")
then in next cell U11 look at =IFERROR(T11,"")

copy paste these from row 3 to row 11.

[cicihk]

No. dont omit >0; becouse it is checking the existance of "Fail" in the range

break the function and evaluate. this may help in understanding. e.g

in one cell say S11 first look at the result of =MATCH("Fail",K11:P11,0)
then in next cell T11 look at =IF(S11>0,"Fail","")
then in next cell U11 look at =IFERROR(T11,"")

copy paste these from row 3 to row 11.

Clear.. Thanks much again!!

[zbor]

It works as well. Thanks. But why you guys put ">0" ?

COUNTIF has another approach.

It COUNTS how many times Fail appear in the range.

It can be:
0 (never appear)
1 (appears once)
2 (appears twice)
3,4,5... etc

If it never appear (0) then it's OK.
If it appears more then zero (>0) write FAIL.

So...

IF( (COUNT how many times appear) is greather then zero write FAIL, otherwise lave blank.

This is by words... How you can tell to excel see formula above.

You could also use:

=IF(COUNTIF(K4:P4, "Fail")=0, "", "Fail")