Could someone explain the purpose of a double negative in the following
formula:

-sumproduct(--Isnumber(match($a$2:$a$15000,Namedrange,0))

Thanks.

Hi,

It is one of several ways of getting the results of the Isnumber function to
be stated in terms of 1 and 0 instead of True and False, so that they can be
used in the calculation

There is a full explanation at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Anthony

"M Moore" wrote:

Could someone explain the purpose of a double negative in the following
formula:

-sumproduct(--Isnumber(match($a$2:$a$15000,Namedrange,0))

Thanks.

makes a string act like a number.

"M Moore" wrote:

Could someone explain the purpose of a double negative in the following
formula:

-sumproduct(--Isnumber(match($a$2:$a$15000,Namedrange,0))

Thanks.

think you mean coerces a boolean to a number. True = 1, False = 0

--
Regards,
Tom Ogilvy

"JLGWhiz" wrote in message
...
makes a string act like a number.

"M Moore" wrote:

Could someone explain the purpose of a double negative in the following
formula:

-sumproduct(--Isnumber(match($a$2:$a$15000,Namedrange,0))

Thanks.

M,

The double negative is used to "coerce," as we say, a non-number type into a
number type. Since only a number (not text, boolean, error value, etc.) can
follow a minus sign, one minus sign forces the coercion to a number. The
other just reverses the sign change caused by the first one. In this case,
it's coercing the boolean values TRUE or FALSE returned by ISNUMBER to 1s or
0s.

To take this further, in this particular case, The MATCH function, for each
cell in A2:A1500, returns an index number for each cell in A2:A15000 where
it found a match in Namedrange, or an #N/A! (I think) for those it didn't.
Then ISNUMBER returns the boolean values TRUE where MATCH did return a
number, and FALSE where it didn't. The -- in front coerces them to their
equivalent 0 (TRUE) and 1 (FALSE) numeric values. Then SUMPRODUCT adds up
the 1s. So you get a count of matches.

The SUMPRODUCT doesn't do any multiplying because there is only one
argument. But it still sums all the items in it. You could also have
substituted SUM (=SUM(--ISNUMBER(MATCH($A$2:$A$15,Namedrange,0))), entered
as an array formula (where you use Ctrl-Shift-Enter instead of just Enter),
but SUMPRODUCT takes care of cycling through all the items in A2:A1500
automatically for you.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"M Moore" wrote in message
...
Could someone explain the purpose of a double negative in the following
formula:

-sumproduct(--Isnumber(match($a$2:$a$15000,Namedrange,0))

Thanks.