Make formula more simple --- array?

[Nikki]

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

 

[Bernd]

Hi Nikki,

If your cells are adjacent:
=IF(SUMPRODUCT(--(A1:A20="PASS"))=COUNTA(A1:A20),"PASS","FAIL")
=IF(SUMPRODUCT(--(A1:A20="FAIL"))>0,"FAIL","PASS")

If not, array-enter for example:
=IF(SUM(--(A1="FAIL"),--(A7="FAIL"),--(A9="FAIL"))>0,"FAIL","PASS")

Regards,
Bernd

 

[Bob Phillips]

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

--
HTH

Bob

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

 

[Guest]

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

 

[Guest]

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

 

[Nikki]

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?

<snip>


Yes, Ron, Thank you; I knew it could be done more efficiently
somehow.

Thanks to everyone else too, but this one fits my needs for this
particular problem (everything being in one row/column).

Nikki

 

[Bernd]

Hi Nikki,

A little warning if you really intend to use some of the suggested
formulas:

Test them by inserting some rows between the cells you compare.

All suggestions are worth to be analyzed and one can learn quite a
lot, I think.

But some of them are vulnerable against insertions/deletions etc...

Regards,
Bernd