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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
SumProduct question
thanks a lot
|
#4
|
|||
|
|||
SumProduct question
You're welcome - thanks for feeding back.
Pete On Nov 14, 3:13*pm, wilson wrote: thanks a lot |
#5
|
|||
|
|||
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 | |
|
|