Expensiveness in ascending order for the oft-used coercers:
--
+0
*1
Plus Zero is closer to double Nots.
Note that all math operations cause coercion lke in
=TRUE^10
Recall also that the syntax for SumProduct is...
SumProduct(X1,X2,...)
where each X must be either a numerical range or a numerical array object.
Given the above syntax,
=SUMPRODUCT(A2:A10,B2:B10)
is faster than
{=SUM(A2:A10*B2:B10)}
but also faster than
=SUMPRODUCT(A2:A10*B2:B10)
where both A2:A10and B2:B10are numerical range objects.
In multiconditional computations (say multiconditional counting), we have often logical array objects to consider, e.g.,
E2:E10="UP" and F2:F10=2003
Lets name such expressions conditionals.
[1]
{=SUM((E2:E10="UP")*(F2:F10=2003))}
would give the desired multiconditional count as would:
[2]
=SUMPRODUCT((E2:E10="UP")*(F2:F10=2003))
[3]
=SUMPRODUCT(--(E2:E10="UP"),--(F2:F10=2003))
[4]
=SUMPRODUCT((E2:E10="UP")+0,(F2:F10=2003)+0)
[5]
=SUMPRODUCT((E2:E10="UP")*1,(F2:F10=2003)*1)
In [1] and [2], the coercion occurs as a side-effect of a mathematical operation (a multiplication as indicated by *).
In [3] to [5], we first coercethe arrays of logical values as result of the conditional evaluations into numerical arrays (as SumProduct needs/expects them), which then get multiplied by SumProduct (the comma in the syntax of SumProduct stands for "vector multiplication") then summed.
The timing results show that coercion by -- leads to less jumpy computations. That is, each run shows the same timing characteristics, compared to a coercion with +0 (coercion with *1 is slower). Average time are almost the same for SumProduct formulas that use these coercers.
The SumProduct formulas would be much faster if SumProduct would implicitly coerce (that is, without the need for an explicit coercer like -- or +0) the results of conditional evaluations into numbers.
