Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct
Hi,
Consider the following examples of the same Sumproduct formula all similarly constructed:- =SUMPRODUCT(N($A$1:$A$20="This")*N($B$1:$B$20="Tha t")) =SUMPRODUCT(($A$1:$A$20="This")^1*($B$1:$B$20="Tha t")^1) =SUMPRODUCT(($A$1:$A$20="This")+0*($B$1:$B$20="Tha t")+0) =SUMPRODUCT(($A$1:$A$20="This")*1*($B$1:$B$20="Tha t")*1) =SUMPRODUCT(($A$1:$A$20="This")*($B$1:$B$20="That" )) All the above work perfectly well and I understand that the first 4 coerce the True/False evaluation to 1 & 0. What I don't understand is when would each be selected in preference to the other and why bother at all when the 5th example works perfectly well. In my simple (and probably incorrectly advised) world the only time I would resort to one of the first 4 would be for a formula like:- =SUMPRODUCT(--($A$1:$A$20="This")) Because =SUMPRODUCT(($A$1:$A$20="This")) would fail but having selected the double unary (as most seem to do) why not the N switch or ^1 for example. D |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct
I forgot to include the double unary version
=SUMPRODUCT(--($A$1:$A$20="This")*--($B$1:$B$20="That")) "Dave" wrote: Hi, Consider the following examples of the same Sumproduct formula all similarly constructed:- =SUMPRODUCT(N($A$1:$A$20="This")*N($B$1:$B$20="Tha t")) =SUMPRODUCT(($A$1:$A$20="This")^1*($B$1:$B$20="Tha t")^1) =SUMPRODUCT(($A$1:$A$20="This")+0*($B$1:$B$20="Tha t")+0) =SUMPRODUCT(($A$1:$A$20="This")*1*($B$1:$B$20="Tha t")*1) =SUMPRODUCT(($A$1:$A$20="This")*($B$1:$B$20="That" )) All the above work perfectly well and I understand that the first 4 coerce the True/False evaluation to 1 & 0. What I don't understand is when would each be selected in preference to the other and why bother at all when the 5th example works perfectly well. In my simple (and probably incorrectly advised) world the only time I would resort to one of the first 4 would be for a formula like:- =SUMPRODUCT(--($A$1:$A$20="This")) Because =SUMPRODUCT(($A$1:$A$20="This")) would fail but having selected the double unary (as most seem to do) why not the N switch or ^1 for example. D |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct
The double unary minus version would usually be
=SUMPRODUCT(--($A$1:$A$20="This"),--($B$1:$B$20="That")) using the comma instead of the multiplication sign, because (as you've pointed out) the multiplication already does the job of coercing to a number. -- David Biddulph "Dave" wrote in message ... I forgot to include the double unary version =SUMPRODUCT(--($A$1:$A$20="This")*--($B$1:$B$20="That")) "Dave" wrote: Hi, Consider the following examples of the same Sumproduct formula all similarly constructed:- =SUMPRODUCT(N($A$1:$A$20="This")*N($B$1:$B$20="Tha t")) =SUMPRODUCT(($A$1:$A$20="This")^1*($B$1:$B$20="Tha t")^1) =SUMPRODUCT(($A$1:$A$20="This")+0*($B$1:$B$20="Tha t")+0) =SUMPRODUCT(($A$1:$A$20="This")*1*($B$1:$B$20="Tha t")*1) =SUMPRODUCT(($A$1:$A$20="This")*($B$1:$B$20="That" )) All the above work perfectly well and I understand that the first 4 coerce the True/False evaluation to 1 & 0. What I don't understand is when would each be selected in preference to the other and why bother at all when the 5th example works perfectly well. In my simple (and probably incorrectly advised) world the only time I would resort to one of the first 4 would be for a formula like:- =SUMPRODUCT(--($A$1:$A$20="This")) Because =SUMPRODUCT(($A$1:$A$20="This")) would fail but having selected the double unary (as most seem to do) why not the N switch or ^1 for example. D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Something better than Sumproduct | Excel Worksheet Functions | |||
Sumproduct Across A Row | Excel Discussion (Misc queries) | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct? | Excel Worksheet Functions | |||
sumproduct | Excel Worksheet Functions |