Hi sebastienm,
"sebastienm"
Sure, there's a price to pay, but even with 50k rows, it's still very
negligeable in 99.9% of my situations compare to spending even 10 minutes
to
figure out that somewhere in the model there is a error and where it comes
from. Or maybe more than that to the person who will have to modify my
model
later on and may encounter issues.
Once again, I hope that except for your very first argument (...99.9%...)
you don't believe in the rest of them as otherwise I would like to ask you a
couple of questions (if you don't mind of course)
1) how exactly does *1 enable you to identify/prevent errors in your
formula?
2) if you claim to unnderstand the way SUMPRODUCT works how come it takes
you 10 min to spot a matematical operator between two conditions, which is
what it is all about? Isn't it easier to remember that any matematical
operator coerces TRUE/FALSE to 1/0 (e.g. *,-,+,^,/) and therefore there is
no need to use *1,+0,-0,^1,/1,--, etc.
As a side-note: my tests with the two formulas (with the 50,000 rows range,
100 instances and with actual data) suggest that there is a 17%
recalculation time difference (12 sec vs 14 sec)
After thinking about it, there might not even be a price to this situation
'* 1'. If i remember correctly, Excel compiles functions as they are
entered;
so with '* 1' there may not even be a computation of the *1, only a forced
cast.
Full sheet recalc occurs at a number of events so there is necessarily a
recalc of all operations in the formula at least when you open the file
(unless you set calculation to manual).
Now i have just tested in xl 2002, =SUMPRODUCT((A1:A133)) when all the
cells are numbers formatted as numbers. It returns 0 without the *1 even
though some values are greater than 3.
The format has nothing to do with this issue, I think you are just looking
in the wrong place. In order to understand that =SUMPRODUCT((A1:A133)) can
not possibly return anything rather than 0 it takes just undestanding that
SUMPRODUCT can not sum the logical values TRUE and FALSE. And thus you have
to coerce them explicitly to 1 and 0. Try to input each of the two formulas
in a cell, select everything that is inside the SUMPRODUCT, press F9 and
compare the two arrays.
Just personal preference... my priorities are more ... keeping my models
easy to debug and optimize by first avoiding volatile functions, avoiding
regular Lookup functions which recomputes when any cell in the source
range
changes (even though it is not the first column, nor the column used for
the
returned value), order my sheets in alphabetical order to prevent
double-computations in XL2k....
this all sounds very dogmatic to me and is also outside the scope of this
conversation I guess.
Regards,
KL
|