mike scott wrote:
On 7 Jun 2008 at 15:49, Richard Detwiler wrote:
mike scott wrote:
...
And I've just spotted that a space character (which of course /looks/
like an empty cell) seems to behave like a numeric zero. Ouch!!!
In what way does a space or a character behave like a zero? I've just
tried putting in a cell in the midst of some numbers, first with a
single letter, than with a space. I tried the SUM function, the AVERAGE
function, and the STDEV function, and all of these ignored the cell with
text.
Me not being clear, I think. Sorry.
try filling a1:a10 with a 1. Then somewhere put a cell with
counta(a1:a10) and another with sum(a1:a10). Now try (1) clearing,
say, a5, and (2) putting an unquoted space in a5. Now suppose
someone is doing some sort of stats with those numbers. I see scope
for well-disguised problems.
Holy cow, you seem to be going to great lengths to contrive problems.
First of all, if someone wants to count how many numbers are in a range,
use COUNT (which counts only numbers), not COUNTA (which counts both
numbers and text entries). I don't see how anyone would set up a formula
COUNTA if he/she just wants to count the numbers in a range. (And then
be surprised when it counted a text entry.)
And as I've shown to my satisfaction (using OOo 2.3 on Windows XP, for
what it's worth), the SUM function you mention above works just fine
with one cell containing a space (appearing to be blank, but really
isn't blank).
As I said -- maybe there's an example somewhere of a function that
incorrectly treats a blank cell as a zero. But in the bit of exploring
that I've done (and the examples that you've used), I haven't found it yet.
I'm not saying there are no instances where a space character isn't
treated like a zero -- just that based on a quick test with the
functions that you seemed to express some interest in, it is ignored,
rather than treated like a zero.
