double minus sign in clever SUMPRODUCTS

[jimrward]

Many a time I see formula suggestions from the demi gods in our midst using the SUMPRODUCT function which contain a double minus sign ("--").

Can one of the aforementioned please explain this apparent double negation.

 

[PaddyD]

see:

http://www.mrexcel.com/board2/viewtopic.php?t=78204&highlight=coerce

...same applies.

 

[Zack Barresse]

Paddy, could you not substitute the "--" for the N fx as it also returns a numeric value? or am i off?

 

[Tom Urtis]

please explain this apparent double negation.

They are a method of coercion, which is a way to convert a Boolean value (TRUE or FALSE) to its numeric equivalent (1 or 0). There are several ways to accomplish it, (adding a zero, or multiplying by 1 as examples). One of the, if not the, fastest and most efficient ways is to use two negation operators together, which changes the sign twice, resulting in a numeric value.

 

[PaddyD]

"Paddy, could you not substitute the "--" for the N fx as it also returns a numeric value? or am i off?"

yep, that could be used.

 

[Jay Petrulis]

Hi,

As with many of the more novel Excel approaches, credit for this goes to Harlan Grove. I think this is the original thread that raises this issue. In typical Harlan fashion, he gets into an argument.

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&safe=off&selm=ez2T7lXjCHA.1688@tkmsftngp09

Harlan is unbelievably good, but also a PITA (especially if you are a certain MS MVP who has offered a number of routines to work with arrays).

 

[Ekim]

Jim,

The key issue is the matter referred to by Paddy – Sumproduct …. “just need an extra step to coerce the logicals to numerics (so that sumproduct has something to sum)”. From the Help file on Sumproduct:
“SUMPRODUCT treats array entries that are not numeric as if they were zeros”.

If you go to the thread referred to by Paddy, and remove the double unary from the Sumproduct formula, the answer is 0 (zero).

My understanding of the double unary is that the first “-” converts true/false to -1/0 (True = 1, false = 0) and the second “-” changes the sign to 1/0.

Regards,

Mike