How to return a value if a range (not a cell) contains a certain v

[Guest]

In following an audit program steps a condition may result as "OK", "N",
"NA", or "Y". I have a table with areas down the left and tests across the
top:

A B C D H
Test1 Test2 Test3 etc. PASS/FAIL
1 Area1 OK OK Y FAIL
2 Area2 OK OK OK PASS
etc

I want the conditional function in the PASS/FAIL cells (H1, H2, etc) to look
through the row to the left and return "FAIL" if there is any cell with "Y"
in it, else "PASS".

How do I get the range as the criteria range, not just one cell? I can get
it to work if I say IF(A1="Y","FAIL","PASS"), but not
IF(A1:G1="Y","FAIL","PASS").
So, I need the test to iterate through a range for the given condition, and
return the value I seek if ever it encounters the trigger criteria.

 

[PCLIVE]

One way:

=IF(COUNTIF(A1:G1,"Y")>0,"Fail","PASS")

HTH,
Paul

 

[Guest]

Try this:

H2: =IF(COUNTIF(A22,"Y"),"FAIL","PASS")

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Bob Phillips]

=IF(COUNTIF(A1:G1,"Y")>0,"FAIL","PASS")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

i just wonder what happen @ "N" or "NA" since it may not mean "OK"...

 

[Guest]

Good question. I didn't set up the criteria, but am evolving the meaning of
these categories. "N" means the test was not done or no exception found, so
the answer should not be "FAIL", and "NA" means there were no instances of
the condition to test, so we again would not choose "FAIL," and again "PASS"
is appropriate.
It's not exactly intuitive, I agree.

Thanks for asking.
clem

 

[Guest]

Yah Clem...since you are doing an AUDIT PROGRAM....the way mostly this is
done is thru a pessimistic rather than optimistic approach.....You may have a
complete non-test "N" yet the result is already pre-defined as PASS...It may
be striking for an auditor...hope u got the real point...