To Fran Kabel

[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

 

[Frank Kabel]

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)

 

[LEB]

Sorry I left the "K" off your first name in the first
post.

 

[JE McGimpsey]

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.

 

[Bob Phillips]

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)

 

[Bernie Deitrick]

JE,

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

Thanks,
Bernie
MS Excel MVP

 

[Frank Kabel]

Hi Bernie
maybe as the formula
=+TRUE
still return TRUE
but
=--TRUE
does return 1

 

[Bernie Deitrick]

Thanks. So JE's statement

All math operators attempt to coerce text or boolean values to numbers, if

possible.

is slightly too generalized.

Bernie
MS Excel MVP

 

[LEB]

Thanks for all the help. It makes sense now.

 

[Frank Kabel]

Hi JE
one could argue if '+ some_value' is a mathematical operation as this
does not change the value at all

 

[Norman Harker]

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.

 

[JE McGimpsey]

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"

 

[JE McGimpsey]

See my other reply.

Thanks. So JE's statement

possible.

is slightly too generalized.

 

[Trevor Shuttleworth]

Frank

this might help. There are lots of examples of SUMPRODUCT but I didn't
notice the "--" explanation

http://homepage.ntlworld.com/ken.wright/page13.html

Regards

Trevor

 

[Harlan Grove]

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

...

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.

 

[JE McGimpsey]

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))

 

[JE McGimpsey]

Should have been = 4 + "2", obviously.

 

[Bernie Deitrick]

Norman,

If we adopt a suggestion:

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

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

 

[Bernie Deitrick]

Thanks, JE, for a very comprehensive and understandable relpy.

Bernie

 

[Norman Harker]

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.