SUMPRODUCT Minus Minus

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK, it works, but why does it?

I mean, if you want to use sumproduct to return a conditional sum, you put the mysterious double negative before the first term and it works, you don't put it and it doesn't.

Is this the first recorded case of two wrongs making a right?
 
Hi Vaughan!

All the -- is doing is converting a Boolean response of TRUE or FALSE
to 1 or 0. Unfortunately the internal multiplication of the arguments
of SUMPRODUCT don't serve to convert the Booleans.

In counting cases you can avoid the use of the -- structure by using a
process of multiplying the implicit IF formulas. However, when using
SUMPRODUCT for summing applications where the range to be summed might
contain text or Booleans.

There's been a fair amount of discussion on the --. Do a search on
"unary minus"
 
SumProduct operates on arrays of numbers. When you want to do a
multiconditional counting, say: count the records where A-range "X" and
B-range "Y"...

=SUMPRODUCT((A2:A5="X"),(B2:B5="Y"))

would leave SumProduct with arrays of logical values, not numbers. Something
like this:

=SUMPRODUCT(({TRUE,TRUE,FALSE,TRUE}),({FALSE,TRUE,TRUE,TRUE}))

Excel numerical equivalents for TRUE and FALSE are 1 and 0, respectively.
Since SumProduct does not convert these arrays into their numerical
equivalents itself, we need to effect the conversion explicitly. Here how it
can be done:

[1] Replace comma with * (put otherwise: switch from the native comma syntax
to the star syntax)...

=SUMPRODUCT((A2:A5="X")*(B2:B5="Y"))

This works because arithmetic operators (+,-,*,/, etc) coerces/converts
their operands's data type, in this case from logical to either 1 or 0.

Keep SumProduct's syntax and force coercion with arithmetic operations
applied to conditionals that evaluate to logical arrays...

[2a]

=SUMPRODUCT((A2:A5="X")+0,(B2:B5="Y")+0)

[2b]

=SUMPRODUCT((A2:A5="X")*1,(B2:B5="Y")*1)

[2c]

=SUMPRODUCT(--(A2:A5="X"),--(B2:B5="Y"))

The foregoing has the merit that they allow us to use the native comma
syntax, which apparently avoids the overhead that the multiplication invoked
by * between conditional expressions. It has also become clear that the
temporal profiles associated with *1, +0, and -- differ: The pick order in
speed is:

-- GreaterThan/EqualTo +0 (almost equal in speed)

+0 GreaterThan *1

That's why you see a gradual change to the comma syntax, accompanied with
the fastest operating coercer.
Is this the first recorded case of two wrongs making a right?
Click to expand...

No. As Norman suggested, check out the threads involving the -- coercer,
which explain how it works.

Vaughan said:
OK, it works, but why does it?

I mean, if you want to use sumproduct to return a conditional sum, you put
Click to expand...
the mysterious double negative before the first term and it works, you don't
put it and it doesn't.
 
keepITcool said:
else use N() function to convert the booleans
Click to expand...
....

If Excel didn't have its 7 nested function call limit this would be a more
viable option. For the sake of flexibility, implicit arithmetic conversion
is usually preferable.
 
Back
Top