To Fran Kabel

  • Thread starter Thread starter LEB
  • Start date Start date
L

LEB

Sorry. I lost track of the thread in which I asked the
question about dashes in a SUMPRODUCT function.

You responded the dashes forced Boolean values to
numbers, and I then asked if there is documentation
anywhere in Excel, so that I can study it more. I've been
searching for the thread, so I could read your answer,
but I can't find it. Would you mind answering Here?

Thanks
 
Hi
there is no documentation in the Excel help file for this (as this is
not the standard usage of SUMPRODUCT).
I hope Ken reads this thread as he has an excellent explanantion for
SUMPRODUCT (and I do not find it at the moment)
 
I'm not Frank, but it's no mystery:

The - is a "minus" operator (see "Calculation operators in formulas" in
XL Help).

All math operators attempt to coerce text or boolean values to numbers,
if possible. Boolean values (TRUE/FALSE) are coerced to 1/0,
respectively, so - coerces

-(TRUE)

to -1 and

-(FALSE)

to 0. The second unary minus operator converts -1 to +1 to come out with
a positive number.
 
Look up this answer to a previous question which covers this ground

http://tinyurl.com/ywdw4


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
JE,

Any reason that -- is used instead of just +?

Thanks,
Bernie
MS Excel MVP
 
Hi Bernie
maybe as the formula
=+TRUE
still return TRUE
but
=--TRUE
does return 1
 
Thanks. So JE's statement
All math operators attempt to coerce text or boolean values to numbers, if
Click to expand...
possible.

is slightly too generalized.

Bernie
MS Excel MVP
 
Hi JE
one could argue if '+ some_value' is a mathematical operation as this
does not change the value at all :-)
 
Hi Bernie!

There is a tendency to use the -- too much to coerce TRUE to 1 and
FALSE to 0.

If we adopt a suggestion:

=SUMPRODUCT(($A$1:$A$4="ProductA")*($C$1:$C$4="Shipped")*($B$1:$B$4))

No -- is required because the operation of TRUE * TRUE etc will
coerce.

However, if we adopt the form:

=SUMPRODUCT(--($A$1:$A$4="ProductA"),--($C$1:$C$4="Shipped"),($B$1:$B$
4))

The -- is required because SUMPRODUCT is a PITA and although it is
multiplying the three elements together it doesn't coerce the TRUE and
FALSE arguments.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
yes - there is no unary plus operator

You can see that when you enter something like:

= 4 - +3

When you look at the formula in the formula bar, XL has changed it to

= 4 - 3

so the parser isn't evaluating it as an operator.

-, or negation on the other hand, is mantained:

= 4 - -3

You can write

=+TRUE

but XL appears to ignores the +, or perhaps it treats it as a formula
lead - since you can enter +TRUE (without the = sign) and the parser
converts it to =+TRUE) - Lotus compatibility, no doubt.

The binary + operator, OTOH, does exist and coerces just fine:

= TRUE + 0

or

= 4 - "2"
 
Any reason that -- is used instead of just +?
Click to expand...
...

Because unary + does nothing. Presumably it's provided for lotus 123
compatibility, and is most often seen at the beginning of formulas, just after
the = Excel adds. It's unfortunate that it does do nothing, but arithmetically
there's nothing to do.
 
Why "too much", Norman?

The thing I don't like about using the * operator is that the array
multiplication happens before the arrays are passed to SUMPRODUCT.

In tests I've run, that makes

=SUMPRODUCT((A1:A1000=D1)*(B1:B1000=D2))

somewhat slower than doing explicit coercion and letting SUMPRODUCT do
both the array multiplication and the summation:

=SUMPRODUCT(--(A1:A1000=D1),--(B1:B1000=D2))
 
Norman,
If we adopt a suggestion:

=SUMPRODUCT(($A$1:$A$4="ProductA")*($C$1:$C$4="Shipped")*($B$1:$B$4))
Click to expand...

I'm all for that, since I almost always use that form - I usually approach
SUMPRODUCT like it is an array-entered SUM

= SUM(($A$1:$A$4="ProductA")*($C$1:$C$4="Shipped")*($B$1:$B$4))

and the only time I really use it is in giving a poster a formula that
doesn't require C-A-D.

Are you aware of any result from SUMPRODUCT that can only be gained using
the -- style rather than the * style?

Thanks,
Bernie
 
Hi JE!

Useful information on speed.

"Too much" was used in terms of it not being necessary with:

=SUMPRODUCT(--(A1:A1000=D1)*--(B1:B1000=D2))

I didn't explain it very well before the third cup of black coffee in
the morning.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top