A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SumProduct question



 
 
Thread Tools Display Modes
  #1  
Old November 14th, 2009, 03:45 PM posted to microsoft.public.excel.misc
wilson[_3_]
external usenet poster
 
Posts: 2
Default SumProduct question

Dear all,

A B
1 Item Number
2 x 1
3 y 2
4 x 3

SumProduct(--(A1:A4="x"),(B1:B4)) gives the correct answer 4.

However, SumProduct((A1:A4="x"),(B1:B4)) gives the incorrect answer,
it incorrectly gives 0.

Any idea why?


Thanks,

Wing
  #2  
Old November 14th, 2009, 03:58 PM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,780
Default SumProduct question

The condition (A1:A4="x") gives an array of TRUE and FALSE, but these
can't be used in arithmetic. The double unary minus, --, converts
these to an array of 1s and 0s, which can be, and each of these is
then multiplied by the array elements in B1:B4.

Incidentally, as you only have one condition then SUMIF would be a
better function to use:

=SUMIF(A1:A4,"x",B1:B4)

Hope this helps.

Pete

On Nov 14, 2:45*pm, wilson wrote:
Dear all,

* * * * * * A * * * * * *B
1 * * * * *Item * * * *Number
2 * * * * *x * * * * * *1
3 * * * * *y * * * * * *2
4 * * * * *x * * * * * *3

SumProduct(--(A1:A4="x"),(B1:B4)) gives the correct answer 4.

However, SumProduct((A1:A4="x"),(B1:B4)) gives the incorrect answer,
it incorrectly gives 0.

Any idea why?

Thanks,

Wing


  #3  
Old November 14th, 2009, 04:13 PM posted to microsoft.public.excel.misc
wilson[_3_]
external usenet poster
 
Posts: 2
Default SumProduct question

thanks a lot
  #4  
Old November 14th, 2009, 05:09 PM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,780
Default SumProduct question

You're welcome - thanks for feeding back.

Pete

On Nov 14, 3:13*pm, wilson wrote:
thanks a lot


  #5  
Old November 14th, 2009, 07:14 PM posted to microsoft.public.excel.misc
Joe User[_2_]
external usenet poster
 
Posts: 757
Default SumProduct question

"Pete_UK" wrote:
The condition (A1:A4="x") gives an array of TRUE
and FALSE, but these can't be used in arithmetic.


I beg to differ with you. If that were true, then --(A1:A4="x") would not
work (i.e. behave as intended) since that is an arithmetic expression.

In fact, the array formula SUM((A1:A4="x")*B1:B4) works just fine. So does
the equivalent non-array formula SUMPRODUCT((A1:A4="x")*B1:B4). Both use
arrays of TRUE and FALSE in the arithmetic.

SUMPRODUCT((A1:A4="x"),B1:B4) does not work because the designers of
SUMPRODUCT made the arbitrary decision that SUMPRODUCT will treat arrays
arguments "that are not numeric as if they were zeros". See the SUMPRODUCT
help page.

Similarly, the array formula SUM((A1:A4="x"),B1:B4) does not have the same
result as the array formulas SUM(--(A1:A4="x"),B1:B4) and
SUM((A1:A4="x")+B1:B4). Again, the designers of SUM made the arbitrary
decision that "[i]f an argument is an array or reference, [... e]mpty cells,
logical values, text, or error values in the array or reference are ignored"
(i.e. treated as zero), as the SUM help page explains.

Note: When I say "arbitrary", I do not mean to imply that their decision
was capricious, at least not for Excel. I simply mean that they made a
choice; it is not something fundamental to computer logic. In fact, to some
degree, the choice in Excel was probably made to be compatible with
market-leading competitors and earlier MS products (e.g. Multiplan).


The double unary minus, --, converts
these to an array of 1s and 0s


That is correct.

But just so there is no misunderstanding, there is nothing special about the
double negation ("--"). Any value-preserving arithmetic operation would
accomplish the same thing, e.g. (A1:A4="x")*1 and (A1:A4="x")+0.

The result of -(A1:A4="x"), single negation, or any other arithmetic
operation is an array of numerical values. Double negation is needed to
restore the original numerical values, just --5 is 5.


----- original message -----

"Pete_UK" wrote in message
...
The condition (A1:A4="x") gives an array of TRUE and FALSE, but these
can't be used in arithmetic. The double unary minus, --, converts
these to an array of 1s and 0s, which can be, and each of these is
then multiplied by the array elements in B1:B4.

Incidentally, as you only have one condition then SUMIF would be a
better function to use:

=SUMIF(A1:A4,"x",B1:B4)

Hope this helps.

Pete

On Nov 14, 2:45 pm, wilson wrote:
Dear all,

A B
1 Item Number
2 x 1
3 y 2
4 x 3

SumProduct(--(A1:A4="x"),(B1:B4)) gives the correct answer 4.

However, SumProduct((A1:A4="x"),(B1:B4)) gives the incorrect answer,
it incorrectly gives 0.

Any idea why?

Thanks,

Wing


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.