What does the double dash ( - - ) do in Excel?

Question

I have some formulas with -- in them in Excel, and experiments confirm that =--1 is indeed a valid formula that evaluates to 1. However, Googling doesn't return anything. What does -- do?

Answer 1

A minus sign changes a positive to a negative in standard excel formulas.

Using two minus signs -- changes TRUE and FALSE to numbers:

=(1>2) =FALSE
=--(1>2) =0
=(2>1) =TRUE
=--(2>1) =1

More importantly, it's great to use in array formulas -

Take this table starting in cell A1

Primary Secondary   Count
red     blue        10
blue    red         20
yellow  red         30
red     blue        40
purple  green       50
blue    red         60
red     red         70

If you want to see how many you have of a specific combination, you can use a sumproduct like this -

=SUMPRODUCT(--(A2:A8="blue"),--(B2:B8="red"),C2:C8)

This will give you 80 by doing this to your table (literally multiplying ) -

blue    red    Count    Total
0   x   0   x   10   =   0
1   x   1   x   20   =   20
0   x   1   x   30   =   0
0   x   0   x   40   =   0
0   x   0   x   50   =   0
1   x   1   x   60   =   60
0   x   1   x   70   =   0

Essentially it's allowing you to use true/false conditions as multipliers in an array. Whereas this formula

=SUMPRODUCT((A2:A8="blue"),(B2:B8="red"),C2:C8)

will return 0. This would be useful for a time when countifs wouldn't work with the summation.

If you're looking for information on google, the term you should search is double dash.

Answer 2

-- isn't an operator, - is the negation operator, but will also cast a boolean to an int. So --TRUE evaluates to 1 and --FALSE evaluates to 0.

Source.