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.

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
insertnamedefine 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

"Scott P" wrote:

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.

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

or

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

HTH
Jason
Atlanta, GA

-----Original Message-----
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.
.

Just another option:

=5*OR(A1:E1=5)

Ctrl+Shift+Enter

HTH
--
Dana DeLouis
Win XP & Office 2003


"Scott P" wrote in message
...
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.

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)

"Dana DeLouis" wrote:

Just another option:

=5*OR(A1:E1=5)

Ctrl+Shift+Enter

HTH
--
Dana DeLouis
Win XP & Office 2003


"Scott P" wrote in message
...
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.

Scott P wrote:
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))

In article ,
says...
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

In article ,
says...
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)

"Dana DeLouis" wrote:

Just another option:

=5*OR(A1:E1=5)

Ctrl+Shift+Enter

HTH
--
Dana DeLouis
Win XP & Office 2003


"Scott P" wrote in message
...
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.