Just a question regarding a formula operator.

[Todd S]

Hi.

I've recently been introduced to the following formula
through Newsgroups:
=SUMPRODUCT(--(Sheet1!$B$2:$B$9000>=1),--(Sheet1!
$C$2:$C$9000=14))

It works for my needs, however, I'm trying to understand
it for other applications.

What do the -- operators do? I've never seen them before
and can't find a reference.

Thanks!!

Todd

 

[J.E. McGimpsey]

The unary minus (-) operator negates values (positive to negative,
negative to positive). Using two in a row restores the sign.

As with any math operator, XL tries to coerce operands to numeric
form if it can. With TRUE/FALSE, XL coerces them to 1/0,
respectively, so

--TRUE ==> --(1) ==> -(-1) ==> 1

--FALSE ==> --(0) ==> -(-0) ==> 0

You could accomplish the same thing with

TRUE + 0 ==> (1) + 0 ==> 1

but double unary minus seems to be a bit faster and has a higher
precedence.

 

[Tom Ogilvy]

Sheet1!$B$2:$B$9000 returns an array of Boolean values (True or False).

applying a math operation to these values converts them to numbers. the
first negative sign converts them to negative numbers and the second
converts them back to positive numbers.


Then sumproduct multiplies the arrays of 1's and 0's together - anywhere you
multiply 1 x 1 you get a 1 otherwise a 0. It then adds these up and gives
you the count of rows matching both positions.

you could do a single conversion for each column in this case since
multiplication of two negatives will produce a positive:

=SUMPRODUCT(-(Sheet1!$B$2:$B$9000>=1),-(Sheet1!> $C$2:$C$9000=14))