Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
Hello all,
Every response that I've seen that includes SUMPRODUCT, I see all of the experts using the "Binary Negetion Operator" (<---please correct my terminology if incorrect). For example: =SUMPRODUCT(--([Some Range =,<,,<=,= Some value or some cell ref]),--([Some Range])) What is the purpose of the "Binary Negetion Operator"? I've been the SUMPRODUCT function for a year or so now and have never used the the double negative. Although, I do use the function in the other manner: =SUMPRODUCT(([Some Range =,<,,<=,= Some Value or Some Cell Ref])*([Some Range])) Thanks for any help anyone can provide, Conan Kelly |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
The double negation simply converts a TRUE/FALSE value to 1/0 so SUMPRODUCT
can actually sum (One negative to convert TRUE/FALSE to -1/0, and the second negative to convert back to positive... 1/0). It is the same as doing any numerical calculation, so you could use =SUMPRODUCT(1*([range1]),1*([range2])) as well. You don't know it but you're doing the same thing in your example, by using ([Range1])*([Range2]) Excel is performing a vector dot product, which is the numerical computation you need. Make sense? -- Regards, Dave "Conan Kelly" wrote: Hello all, Every response that I've seen that includes SUMPRODUCT, I see all of the experts using the "Binary Negetion Operator" (<---please correct my terminology if incorrect). For example: =SUMPRODUCT(--([Some Range =,<,,<=,= Some value or some cell ref]),--([Some Range])) What is the purpose of the "Binary Negetion Operator"? I've been the SUMPRODUCT function for a year or so now and have never used the the double negative. Although, I do use the function in the other manner: =SUMPRODUCT(([Some Range =,<,,<=,= Some Value or Some Cell Ref])*([Some Range])) Thanks for any help anyone can provide, Conan Kelly |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
Have a look at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html this gives a very detailed explanation on all aspects of the sumproduct formula. Greetings from New Zealand "Conan Kelly" wrote in message ... Hello all, Every response that I've seen that includes SUMPRODUCT, I see all of the experts using the "Binary Negetion Operator" (<---please correct my terminology if incorrect). For example: =SUMPRODUCT(--([Some Range =,<,,<=,= Some value or some cell ref]),--([Some Range])) What is the purpose of the "Binary Negetion Operator"? I've been the SUMPRODUCT function for a year or so now and have never used the the double negative. Although, I do use the function in the other manner: =SUMPRODUCT(([Some Range =,<,,<=,= Some Value or Some Cell Ref])*([Some Range])) Thanks for any help anyone can provide, Conan Kelly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct question | Excel Discussion (Misc queries) | |||
A SUMPRODUCT question | Excel Discussion (Misc queries) | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) |