Make formula more simple --- array?
Ooops! I only pasted part of the formula. Here's the whole thing:
=IF(SUMPRODUCT(--(ROW(W4:W27)={4,8,13,16,22,27})*(W4:W27="PASS"))=6 ,"PASS","FAIL")
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Ron Coderre" wrote:
Maybe something like this?:
=SUMPRODUCT(--(ROW(W4:W27)={4,8,13,16,22,27})*(W4:W27="PASS"))
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
"Nikki" wrote:
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
|