What does the "--" do?

[thekovinc]

First off, I wasn't sure where to post this because I didn't know if the -- is classified as a worksheet function or not, but what does it do? I've seen them in a bunch of posts. I am just curious what they do.

Example:

=SUMPRODUCT(--($D$1:$D$100="100"),$F$1:$F$100)

versus

=SUMPRODUCT(($D$1:$D$100="100"),$F$1:$F$100)

Why does the second one not work?

Thanks,
Nick

[Dave Peterson]

-- can be used to convert non-numerics to numbers.

If A1 contained the text '123, then
=--A1 will contian the number 123.

It also can be used to convert True/False to +1/0. (-true = -1, --true = +1)

=sumproduct() likes to work with numbers so the first formula gets those
trues/falses converted to 1/0s.

But you can also write the formula this way:
=SUMPRODUCT(($D$1:$D$100="100")*($F$1:$F$100))

Just the act of multiplying is enough to coerce the true/false to 1/0.

=0+A1
=1*a1
will do the same kind of thing in that first example.

thekovinc wrote:
>
> First off, I wasn't sure where to post this because I didn't know if the
> -- is classified as a worksheet function or not, but what does it do?
> I've seen them in a bunch of posts. I am just curious what they do.
>
> Example:
>
> =SUMPRODUCT(--($D$1:$D$100="100"),$F$1:$F$100)
>
> versus
>
> =SUMPRODUCT(($D$1:$D$100="100"),$F$1:$F$100)
>
> Why does the second one not work?
>
> Thanks,
> Nick
>
> --
> thekovinc
> ------------------------------------------------------------------------
> thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378
> View this thread: http://www.excelforum.com/showthread...hreadid=490898

--

Dave Peterson

[thekovinc]

Thank you very much!

[Roger Govier]

Hi Nick

The double unary minus "--" coerces the TRUE or FALSE result of the
comparison inside the first set of brackets into 1's or 0's. This could also
be achieved by adding +0 or multiplying with a *1 addition to the formula
but doing a minus, minus (which comes back to the original value) is a
little faster in execution.

The resulting 1's and 0's then multiply the array from the second range and
Sumproduct adds the results to give the final answer.

So if the value in cell D1 were 100, that would be TRUE, and coerced to 1
would be multiplied by the value in F1 would result in the value in F1.
If D2 were not 100, then it would be FALSE hence 0 which multiplying by the
value in F2 would result in 0.


Regards

Roger Govier


thekovinc wrote:
> First off, I wasn't sure where to post this because I didn't know if the
> -- is classified as a worksheet function or not, but what does it do?
> I've seen them in a bunch of posts. I am just curious what they do.
>
> Example:
>
> =SUMPRODUCT(--($D$1:$D$100="100"),$F$1:$F$100)
>
> versus
>
> =SUMPRODUCT(($D$1:$D$100="100"),$F$1:$F$100)
>
> Why does the second one not work?
>
> Thanks,
> Nick
>
>