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.
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.
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.
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.
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 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 ;-)
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.
I very much do agree that the documentation should explain the behavior, and warn of the traps involved.
-- Jan Wilson, Belize
