#### 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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### 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
149
Replies
11
Views
1K
Replies
0
Views
105
Replies
3
Views
519
Replies
6
Views
225

1,172,010
Messages
5,878,711
Members
433,366
Latest member
kaaryy

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

### Which adblocker are you using?

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