- - is a double unary operator that coerces the calculation.

[mintjulep]

Reference thread68-1542050

In the referenced thread Skip offers a SUMPRODUCT solution that makes use of the --(BOOLEAN) to coerce a numerical 1 or 0 for the product part of the SUMPRODUCT.

Anotherhiggens was kind enough to 'splain it to me.

But why is it necessary?

This seems to work: =(A2=MONTH(TODAY()))*MONTH(A1)

Excel seems to know that TRUE is 1 and FALSE is 0.

 

[mintjulep]

SUMPRODUCT treats array entries that are not numeric as if they were zeros.

Ok, that explains the why, but not the WHY.

[rhetorical]Why can't Excel treat a BOOLEAN data type the same in all functions and operations?[/rhetorical]

 

[SkipVought]

Set up a simple count formula with some a's in some cells
[tt]
=sumproduct((A1:A11="a"))
[/tt]
You get ZERO
[tt]
=sumproduct(-(A1:A11="a"))
[/tt]
You get a NEGATIVE count of occurences of a's (-1*array)
[tt]
=sumproduct(--(A1:A11="a"))
[/tt]
You get the COUNT of occurrences of a's (-1*-1*array)

That's my definition or coersion.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

=("a"="a") +1 returns 2

=SUM((1=1),1) returns 2

=SUM((1=2),1) returns 1

=PRODUCT((1=1), 2) returns 2

=PRODUCT((1=2), 2) returns 0

So why do operators and some functions think that TRUE is equivalent to a numerical 1 and FALSE is equivalent to 0, but SUMPRODUCT doesn't?

 

[SkipVought]

=("a"="a") +1 returns 2
1 +1

=SUM((1=1),1) returns 2
1 ,1

=SUM((1=2),1) returns 1
0 ,1

=PRODUCT((1=1), 2) returns 2
1 , 2

=PRODUCT((1=2), 2) returns 0
0 , 2

Self explanitory


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

I think the answer to WHY? (rather than why?)is that the developers never envisaged sumproduct being used in this way (with true-false). If you were merely trying to sum products then it makes sense to treat other non-numeric values as zero. They didn't think of telling it how to evaluate True and False in this function.

Gavin

 

[yogia]

Hi minjulep:

My take on this is that if we take an array of TRUE and FALSE values these will have to be coerced into 1s and 0s whether you use the SUM function or SUMPRODUCT function.

By the way, I can coerce TRUE and FALSE into 1 and 0 by using not only
--() but also
()+0
()*1
()/1
()^1

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com