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.
To explain for those who don't use Calc, the problem is that one can format a cell as text and have it hold a character string made up of numbers. Logically, it is not a number but just another name, perhaps a social insurance number or a parts number for a stereo. Or it could be a postal code or a telephone number.
At the moment the Guide Chapter . Introducing Calc states:
Entering numbers as text
If a number is entered in the format 01481, Calc will drop the leading 0. To preserve the leading zero, in the case of telephone area codes for example, precede the number with an apostrophe, like this: '01481. However, the data is now regarded as text by Calc. Arithmetic operations will not work on it. It will either be ignored or will produce an error of some kind.
Unfortunately '01481 x 5 = 0. It is neither ignored nor does it produce an error. It should produce a #Value error.
Below are some examples of operations on text 'numbers' on a real number both for Calc and Excel. In my opinion the results from both of these are unacceptable and, potentially, very, very dangerous.
Calc Version 2.0.0 (Final)
Check of arithmetic operations on text cells.
A1
= 5 A2 = foo C1=999 <- C1 is a Text
formated cell
Operation Result
Add A1 + B1
5
Sum
(A1:B1) 5
Multiply A1*B1 0
Sum (A1:C1) 5
Add
A1 + C1 5
Sum(A1:C1) 5
Multiply A1+C1
0
All of these
operations should have returned an error of #Value.
The equivalent results from Excel (2003) are
A1
= 5 B1 = foo C1 = 999 <-
C1 is a Text formated cell
Operation Result
Add A1 + B1 #VALUE!
Sum (A1:B1) 5
Multiply A1*B1 #VALUE!
Add
A1 + C1 #VALUE!
Sum
(A1:C1) 5
Multiply A1*C1
4995
All of these operations should have
returned an error of #Value.
I will be logging this as an issue but
I wanted to bring it to OOoAuthors attention as well. I think that
we a) need to amend the Guide and b) consider putting in a
warning about this behaviour and the behaviour of Excel.
-----
John Kane
Kingston ON Canada
[EMAIL PROTECTED]
(613)888-2399
--
http://www.fastmail.fm - Or how I learned to stop worrying and
love email again
