Excel formula question

If I have a number of contiguous cells in one column(eg H30 to H36), and each one has a formula that returns either the word pass, or the word fail, and I want one other cell to return the word PASS if all the other cells contain pass or fail if one or more of the other cells contain the word fail, I can do it by using something like =if(or(h30 <> pass, h31 <> pass, h32 <> pass, h33<>pass, h34<>pass, h35<>pass, h36<> pass, "FAIL", "PASS")
(the syntax might not be bang on here, as I am using memory from work)

How can I condense all of the cells into something like (h30:h36) and still have it work.

This is not a big deal, as it works the way it is, but it bugs me that I can't get it, using AND or OR

 

=AND(H30:H36) will work, as excel treats TRUE and FALSE as valid logical values

 

I don't know, I tried
=IF(OR(H30:H36)<> pass, "FAIL" "PASS)
and it didn't work. Neither did
=IF(AND(H30:H36)= pass, "PASS" "FAIL")


(again, syntax might not be perfect, but you can see what I'm getting at.

Thanks a lot for any help.

 

See attached, if you change A1, B1 or C1 the result in D1 will change.

 

Thanks lesrae. It will be a while before I can try it. I sent the .xls file to my work pc, but I won't be in there until Thursday. I'll let you know how it turns out.

 

OK - This is all it is:

 

When I tried this, it returned a zero regardless of what was in the precedent cells

Could it be because the word "pass" or "fail" in the precedent cells is decided by a formula?

If you find this an interesting problem then please help, and thank you, but don't waste too much of your time on it as it is not critical.

 

My fault - missed the fact you were using pass/fail and not true/false.

Convert the words pass/fail to true/false, then perform the AND function against them, then convert the result back to pass/fail.

In the attached, A1 to A4 are the result of a check on the numerics in D1 to D4, exam results as an example, this shows that it doesn't matter if the source data is a formula.

B1 to B4 are A1 to A4 converted to boolean operators.

B5 is the AND result of B1 to B4.

A5 is it converted back to pass/fail.

It's a bit long winded, but should work. You could hide the true/false column if you want to clean up your sheet.

 

Thanks a lot for your help on this, lesrae.

I'll experiment with what you have shown me.