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.
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.
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.

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....
I think it's not always practical to optimize a model on all aspects. Eg: i
wrote some of my usual custom functions in C, but most of the time, i need
something quick, so i code a function in vba, even though it's going to
compute 20 times slower than in C... in the end it's still a fraction of a
second of computation (or say 1 second) compared to the time it takes to
writting/testing/modifying/accessing it with C.
Just personal preference :-)

--
Regards,
Sébastien


"KL" wrote:

Hi sebastienm,

but i prefer to keep the '* 1' so that i don't spend too much time
searching
the source of a 'wrong' result.

I hope you are also aware what is the price you are paying for this. Just
imagine that, in the original formula you proposed, instead of range B1:B100
you work with B1:B1000 (which I think is more realistic). So by adding *1 to
your formula you effectively add 1000 additional (unnecessary) operations as
each member of the array will be multiplied by 1. Now a few what-if's: 1.
there are 10,000 rows involved, 2. there are more than one instance of the
formula, 3. there are more than one sheet with several instances of the
formula involving thousands of cells?

=SUMPRODUCT((LEN(A4:A7)3) * 1)
--- here, you get the right result.

Agree, this can also be achieved by double minus:

=SUMPRODUCT(--(LEN(A4:A7)3))

Regards,
KL