double minus sign in clever SUMPRODUCTS

jimrward · Feb 19th, 2004, 11:54 PM

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 · Feb 19th, 2004, 11:58 PM

see:

http://www.mrexcel.com/board2/viewto...ghlight=coerce

...same applies.

Zack Barresse · Feb 20th, 2004, 12:02 AM

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

Tom Urtis · Feb 20th, 2004, 12:03 AM

Quote:

Originally Posted by jimrward

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 · Feb 20th, 2004, 01:19 AM

"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 · Feb 20th, 2004, 01:38 AM

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&l...%40tkmsftngp09

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 · Feb 20th, 2004, 02:57 AM

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

Feb 19th, 2004, 11:54 PM	#1
jimrward Join Date: Feb 2003 Location: Sussex, Englandshire,UK Posts: 759	double minus sign in clever SUMPRODUCTS 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.

Feb 20th, 2004, 12:02 AM	#3
Zack Barresse MrExcel MVP Join Date: Dec 2003 Location: Oregon, USA Posts: 9,797	Paddy, could you not substitute the "--" for the N fx as it also returns a numeric value? or am i off? __________________ Regards, Zack Barresse All Excel Functions (If you would like comments in any code, please say so.)

Feb 20th, 2004, 01:38 AM	#6
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Re: double minus sign in clever SUMPRODUCTS 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&l...%40tkmsftngp09 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). __________________ Bye, Jay

Feb 20th, 2004, 02:57 AM	#7
Ekim Join Date: Jul 2002 Location: Perth, Australia Posts: 1,416	Re: double minus sign in clever SUMPRODUCTS 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 19th, 2004, 11:58 PM	#2
PaddyD MrExcel MVP Join Date: May 2002 Posts: 12,819	see: http://www.mrexcel.com/board2/viewto...ghlight=coerce ...same applies.

Feb 20th, 2004, 01:19 AM	#5
PaddyD MrExcel MVP Join Date: May 2002 Posts: 12,819	"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.