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
