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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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

Replies
3
Views
147
Replies
11
Views
956
Replies
0
Views
95
Replies
3
Views
509
Replies
6
Views
222

1,171,088
Messages
5,873,749
Members
432,998
Latest member
RachCur

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