Multiple OR function

[Scott P]

Is there some way to consolidate a function such as this:
=IF(OR(A1=5,B1=5,C1=5,D1=5,E1=5),5,0)? I want to see if any one of a
non-contiguous range of cells is equal to a fixed value. I would like to
input the fixed value only one time into the function if possible.

Thanks.

 

[Peo Sjoblom]

In your example you aren using a contiguous range, if that's the case use

=(COUNTIF(A1:E1,5)>0)*5

if you really meant non-contiguous range as in A1, C1, E4 etc you can select
all cells in question (hold down ctrl while selecting them) then do
insert>name>define and name them to let's say MyRange, then you can use

=(SUMPRODUCT(--(LARGE(MyRange,ROW(INDIRECT("1:"&COUNT(MyRange))))=5))>0)*5

note that the latter will return an error if all cells are blank or if it no
numbers in the cells




Regards,

Peo Sjoblom

 

[Jason Morin]

=IF(SUMPRODUCT(--(A1:A5=5)),5,0)

or

=SUMPRODUCT(--(A1:A5=5))*5

HTH
Jason
Atlanta, GA

 

[Dana DeLouis]

Just another option:

=5*OR(A1:E1=5)

Ctrl+Shift+Enter

HTH

 

[Scott P]

Thanks, Dana. I actually found this to work as well based upon your post:

=IF(OR(A1:E1=5),1,0) ---> this is entered as an array (CTRL+SHIFT+ENTER)

 

[Aladin Akyurek]

Thanks, Dana. I actually found this to work as well based upon your post:

=IF(OR(A1:E1=5),1,0) ---> this is entered as an array (CTRL+SHIFT+ENTER)

[...]

If that's what you want, the following would be less costly:

=--ISNUMBER(MATCH(5,A1:E1,0))

 

[Tushar Mehta]

Thanks, Dana. I actually found this to work as well based upon your post:

=IF(OR(A1:E1=5),1,0) ---> this is entered as an array (CTRL+SHIFT+ENTER)

Yes! Obvious, straightforward, and to the point!

It seems that lately 'experts' have fallen so much in love with their
own technical complexities that they seem to overlook the obvious
solution that also happen to be transparent, simple, and easy to
understand.

Of course, no one except Peo addressed the original point about non-
contiguous ranges. And, honestly, I prefer spelling out the condition
with something like

=IF(OR(A1:E1=5,G1:K1=5),5,0)

to Peo's

=(SUMPRODUCT(--(LARGE(MyRange,ROW(INDIRECT("1:"&COUNT(MyRange))))=5))>
0)*5

which I won't even attempt to understand. <g>

[OK, OK, that's not true. It's not *that* difficult to figure out and
is a slick way to get to every numeric value in a non-contiguous range.
Though, I still dislike the current love affair with double-negation
and SUMPRODUCT. The array formula =OR(LARGE(myRng,ROW(INDIRECT
("1:"&COUNT(myRng))))=5) works just nicely, thank you.]

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions