Just a precision before starting here; i was just discussing my point of view
in my situations, that's it; not saying that's the way things are, just
sharing observations, and not claiming that i KNOW how things work... far
from that.
So if the lack of emoticons, my english vocabulary and my way of putting
sentences in English (which i though was quite ok) seemed misplaced or rude,
i apologize.
Now, that said...
1)how exactly does *1 enable you to identify/prevent errors in your formula?
By using *1, in my mind, i know that the goal is to Count; just easier to
me when debugging with many conditions in the expression. But to be honest, i
also thought it used to be necessary in some specific situations. I cannot
find any right now though, so it is possible that i learned something wrong
someday long ago.
Anyway, when distributing the book, i think it's easier for other
users/owners to figure out what is going on, but well, seems like it wouldn't
be an issue for you.
2) takes 10mns...between two conditions
First of all, to make things clear, i don't claim to know how the function
works. Now, i was not speaking of 'between two conditions' but when debugging
a model in general. Depending of the size of a model, it is not necessarily
obvious to pinpoint issues within the minute. Even less when you are given a
book that someone else has built; personaly i have to deal with foreign books
almost everyday at work.
3) sheet recalc.
It has nothing to do with sheet recalc or auto/manual setting. If excel
compiles expressions once when entered ...[can anybody can confirm? i'd like
to know.]. Some compilers tend to remove extra unnecessary stuff: compiling
'number*1' to 'number, 'number+0' to 'number..... Not sure how excel's
compiler works (this is why i said 'might not even be'), but i would think it
is intelligent enough to compile 'number * 1' to 'number'.
4) The format has nothing to do with this issue
hmmm... that's what i was trying to say ... everything being numbers meaning
that the cause of the different results wouldn't come from formats for sure
.... it was just to describe the situation.
5)dogmatic
.... maybe ... in some ways...
At least, i've learned that there seems to be no exception to the coersion.
--
Regards,
Sébastien
"KL" wrote:
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, S 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
|