Double dash ... what is it?

**excelname** · 06-02-2010, 10:00 AM

In a solution here I saw the following:

=SUMPRODUCT(--(TEXT($B$3:$C$184,"ddd")=M2))

I don't know what the double-dash between the open parens is, or does, and can't find it via help.

Can you offer or point me toward an explanation?

Thanks

**martindwilson** · 06-02-2010, 10:04 AM

see here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

**excelname** · 06-02-2010, 10:29 AM

Originally Posted by martindwilson

see here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Thank you Martin,

At the page you cited I learned that the "double dash" is called a double unary operator. Not being clever enough to divine its function from the context, and not knowing what a unary operator is, I did a little research and found this:

3 - -2

In this expression there are two operators that look like this: '-'. They are not the same. The first one, (left most), is the binary subtraction operator. The other, (right before the 2), is the unary negative sign operator.

From an expression evaluation viewpoint, things proceed as follows. The negative sign operator has precedence over the subtraction operator, so it accepts operands first. It accepts only one operand, since it is a unary operator. It accepts the value of two and produces a value of negative two. Now, the subtraction operator gets its chance to accept values. It takes the three and from it subtracts the negative two. This produces five, represented by the number 5, so we can write:

5 = 3 - -2

Now, this expression also equals five:

3 + 2

Since two expressions both equaling five equal each other we can write:

3 + 2 = 3 - -2

But this example isn't a *double* unary but rather a unary and a regular minus sign, as I understand all this so far.

I'm left wondering what the double unary does, and especially what it does in Excel in case that varies at all from what it might do in some simply arithmetical setting.

Many thanks to Martindwilson and/or whomever can help me get clear on this double unary operator and its role in Excel formulas.

**ChemistB** · 06-02-2010, 10:41 AM

The Unary Operator is used to "force" excel to convert "True/False" statements into 1/0 statements. for example Text (A2,"ddd")=M2 would result in True or False. Let's say True.
A single Unary Operator will Convert True to -1 and the double Unary Operator will convert back to 1. Hope that helps.

**shg** · 06-02-2010, 10:42 AM

Excel has six arithmetic operators: + - * / ^ and %. The - serves as both the (binary) subtraction operator and the (unary) negation operator. The distinction is significant, because the negation operator has higher precedence than any other (which is why =-3^2 returns 9, to the eternal annoyance of mathematicians).

When non-number values (logical expression and strings that look like numbers) are part of an arithmetic expression, Excel must coerce them to numbers to complete the evaluation.

The - converts True to -1 and False to 0 (enter =-TRUE in a cell and test). Applying it again converts -1 to 1 and 0 to 0. So the double negation operator converts True to 1 and False to 0 (enter =--TRUE in a cell and test).

So the -- in --(logical expression) converts the expression to 1 if True and 0 if False.

The reason that the parentheses MUST surround a logical expression is to force it to evaluate before the negation. So

=TEXT("6/2/2010","ddd")="Wed" returns True

=--TEXT("6/2/2010","ddd")="Wed" returns #VALUE, because the negation is applied to the text "Wed", which fails

=--(TEXT("6/2/2010","ddd")="Wed") returns 1

The double negation appears commonly, but any arithmetic operation will do:

=TRUE+0 returns 1
=TRUE*1 returns 1
=TRUE/1 returns 1
=TRUE% returns 0.01 (not sure of the utility of that ...)

**excelname** · 06-02-2010, 10:44 AM

Originally Posted by ChemistB

The Unary Operator is used to "force" excel to convert "True/False" statements into 1/0 statements. for example Text (A2,"ddd")=M2 would result in True or False. Let's say True.
A single Unary Operator will Convert True to -1 and the double Unary Operator will convert back to 1. Hope that helps.

Very good. Thanks.

<Reputational props assigned to all respondents!>