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
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
Last edited by excelname; 06-02-2010 at 10:48 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
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.
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.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
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 ...)
Last edited by shg; 03-20-2011 at 06:48 PM.
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks