=IF(SUMPRODUCT(--(T(OFFSET(W4:W100,{0,4,5,6},0,1,1))="PASS"))0,"PA SS","FAIL")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Nikki" wrote in message
oups.com...
Hi group,

I just have a quick question. I understand arrays (mostly?) and have
found their use extremely convenient especially when typing out a
"megaformula". However, I have this situation that has been bugging me
lately in my various Excel work.

Take the following formula: =IF (OR (W4 = "FAIL", W8 = "FAIL", W13 =
"FAIL"), "FAIL", "PASS")

That works perfectly, but it becomes a hassle when there's more than 3
values I'm comparing.

Of course this can also be written: =IF (AND (W4 = "PASS", W8 =
"PASS", W13 = "PASS"), "PASS", "FAIL")

Same thing, works perfectly, but also becomes a hassle when there's
more than 3 cells I'm comparing.

What I'm looking for is a way to shorten this formula, like an array
does with countless AND() functions. (W4:W8 -- except in this case the
cells are not next to eachother) Make sense? Is that possible?

Thank you in advance,
Nikki