I've been reading this thread with interest, but until now, I didn't go
into Calc myself to give myself a first-hand understanding of the
situation.
Now that I've done so, I'm not convinced this is a major problem.
The situation, as best as I can tell, is that in a formula or in
statistical computations, Calc treats a text entry the same way it
treats an empty cell. This is the case whether the text is a word, or a
number formatted as text.
If I have text (including a number formatted as text) in A1, and the
number 5 in B1, then the sum of these cells will be 5, the product of
these cells will be zero, and the average of these cells will be 5. The
exact same answers will occur if cell A1 is empty.
As far as my meager reasoning goes, that's how I would like it. If I
have empty cells or text in the range of data I'm doing a sum of, or
doing statistics on, for example, I would want these cells to be
ignored. I wouldn't particularly want an error message.
I cannot find a situation were Calc tries to convert a number entered
as text, back to that number.
Now, there WOULD be issues when things like phone numbers and social
security numbers are entered as numerical data, which I'm guessing
would be the most common way of entering them. Then someone, if not
careful, could wind up doing meaningless computations like the sum of
phone numbers of the standard deviation of social security numbers. But
I don't know any real way around that. If someone is so sloppy with
what their ranges are for computations, there are all kinds of other
errors that could be made. Probably a much more serious error, because
it would be tougher to detect from the answer, would be doing a
computation on numbers that make sense but which are, for example, one
column removed from the intended target.
So unless I'm totally missing something, I don't see this as a problem.
I do agree it should be documented, of course.
Something on this general topic that I DO think may be a probem (or at
least it's an annoyance) is that if I have a numerical entry, and THEN
try to format it to text, it refuses to be re-formatted, and it remains
a number. In this case, if someone isn't alert, he or she could THINK
that they've formatted these numbers as text, when in fact they are
still numerical data.
-----Original Message-----
From: John Kane <[EMAIL PROTECTED]>
To: authors OpenOffice Authors <[email protected]>
Sent: Mon, 05 Dec 2005 20:15:35 -0500
Subject: Re: [authors] Serious problem in Calc that we should address
in the Guide
On Mon, 05 Dec 2005 18:16:06 -0600, "Jan Wilson" <[EMAIL PROTECTED]> said:
John Kane wrote:
> In reading on the OOo forums I noticed a very serious problem in
> Calc. One can multiply a text cell by a number and get a
> numerical answer rather than an error. I think it is fair to say
> that I was horrified. Even worse in a way, I was told, and
> confirmed, that Excel also does this albeit in a different way.
I understand the problem, but I kinda like the way it is. If you
understand that a spreadsheet is like a weakly typed programming
language, then type conversion will take place automatically. In
such a
language, if you try to perform mathematical operations on text,
the
program will do it if it seems to make any sense.
Unfortunately I don't see that there is a case of it making sense.
Converting a postal code into a number is not sensible and potentially
dangerous.
Spreadsheets are used for all sorts of work including, unfortunately,
such things as statistics, financial modelling, policy analysis and
engineering. I really don't like the idea of someone with five or six
massive 240 page spreadsheets and maybe 500 or 1000 variables of
different types unwittingly multiply by a string variable.
Taking a slightly implausible example I might have two single-cell data
ranges SIN - my social insurance number and STN - Signal to noise ratio.
If SIN ="999999999" and STN = 0 then SIN * 5 =0 and STN *5 = 0. This
is something that could easily slip by in testing with a small data set.
(Actually SIN's used to be used as Gov't ID's so it is not all that
implausble)
Spreadsheets can be hard enough to debug in any event and something like
this just increases the possibility of an error that even fairly
rigorous checking may miss.
That happens, for example, on date entry ... if the program can
interpret a string of text as a date, it automatically converts it to
a
serial number representing the date, while also formatting the cell
to
match the string you entered. Within reason.
Something I am not too keen on but at least you can visually inspect the
data as see what it happening. I would prefer strong typing if that is
the correct term.
Text that cannot be resolved to a number has a value of zero. That
is
quite handy, for example, when you have text labels above and
interspersed within a column of numbers, you can do a sum function on
the whole column and get the sum you expect, rather than error
messages.
Are you sure of that? It appears to me that in Calc the text in a sum()
or count() is simply ignored and the correct sum and N is calculated.
But to be honest I am not a great user of spreadsheets most of the
time.
It does make it more difficult to spot an error, but it makes it
easier
to create formulas if you are careful what you're doing.
Very careful indeed and that is the problem. See my example above. The
problem is, from my point of view that as a safety precaution I would
want to have text variables formatted as text and numericals as
numericals to avoid such a problem. Unfortunately Calc and Excel seem
to set the user up to make mistakes. Multiplying/adding a string
variable by a number should be impossible and, not only is it not, it
does not even give a constant error (like 0) in either Calc or Excel.
What would be ideal, of course, would be settings where you could
choose
whether you wanted to allow automatic conversion of text to numbers
in
numerical formulas, whether you want real text to be regarded as a
zero
in numerical formulas.
I would have to disagree strongly here. I would like to see a fairly
easy way to convert from text to numerical but I think an automatic
conversion is almost certain to cause errors.
I don't suppose anyone would object to type conversion in the other
direction, for example =7&"up" ... but that is handy too, as is the
capability to drag the lower right corner of a cell with "Box01" to
produce "Box02", "Box03", etc., which is kind of a mixed type ;-)
I don't think I understand this. You mean a conversion from numeric to
string?
If you SPECIFICALLY entered something as text, like starting 01481
with
an apostrophe, then neither Excel nor Calc should automatically
convert
it to the number 1481.
That is exactly what it does when you multiply or add. It looks like
text (i.e. left aligned and leading zero) but in Calc '01481 * 2 = 0
and '01481+2 = 2.
In a small spreadsheet this is possibly not a big problem. We would
probably catch it. However spreadsheets are used for all sorts of work
including, unfortunately, such things as statistics, financial
modelling, policy analysis and engineering. I really don't like the
idea of someone with five or six massive 240 page spreadsheets and
maybe 500 or 1000 variables of different types might unwittingly
multiply by a string variable.
That could be your mortgage or my drug dosage that is being calculated
on an ID number or street address.
I very much do agree that the documentation should explain the
behaviour,
and warn of the traps involved.
> --
Jan Wilson, Belize
Thanks. I hope you see my worries.
-----
John Kane
Kingston ON Canada
[EMAIL PROTECTED]
(613)888-2399
--
http://www.fastmail.fm - IMAP accessible web-mail
