jimrward

Well-known Member
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.

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Zack Barresse

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

Tom Urtis

MrExcel MVP
jimrward said:
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.

MrExcel MVP

"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.

Ekim

Well-known Member
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,951
Messages
5,856,477
Members
431,817
Latest member
db74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back