[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: Re: [office-formula] Our next adventure: Types and conversions
Hi Tomas, On Mon, May 22, 2006 at 09:58:47 +0200, Tomas Mecir wrote: > >Next up: how do they interact? In particular: if a function > >expects a Number but gets a Text value, then what? > > Mmm, our long conversations with Eike are going to start again, it seems. :-) Based on my experience with OOo's bug tracking system this indeed is one of the main differences that keep people mourning about "my spreadsheet calculates different", just because they don't notice that text doesn't calculate, respectively calculates as 0 in some cases. This is something we'll have to address. > >A. Text auto-converted to Number. Excel & Gnumeric > > A1. Conversions usually work using the > > "current locale" > > A2. An alternative would be to save the > > locale of a spreadsheet on creation, and ALWAYS use that > >B. Text converted to 0. Lotus 1-2-3 does this. > >C. Text converted to 0 if via reference, and auto-converted > > to number if in-line. OOo 2.0 does this. > >D. Text converted to error. Eike thinks this would be safest, > >E. Allow some set of the above. > >I recommend (E) with "all of the above except A2". > > Well, my opinion is the same. C only adds inconsistency - why should > references be any different from constants ? Seconded. > B and D are basically the same, but IMO they just restrict > functionality where this isn't necessary. I don't consider D being basically the same as B. B goes unnoticed, whereas D clearly indicates an error the user either has to correct or won't get a result at all. > I personally am for A1. The primary thing to consider here would be, > who is going to be affected ? Everyone who shares documents between different locales. A thing I do almost every day. Also see .sig ;-) I thought we had been through that already? > First of all, this will NOT affect regular typing of numbers into > cells, because every spreadsheet automatically parses entered text, so > if a number is entered, it will be parsed with the current locale, > and then stored in the file using something like the C-locale. In many spreadsheet applications input is not interpreted if the cell was already formatted as text. This happens often, and mostly unnoticed by the user if not aware of the difference between automatically left justified for text and right justified for number. Excel, Gnumeric and OOoCalc do this, for example. > Hence, the only two cases where this will affect anything would be: > 1. ="3"+3 (either of these could be a product of some function, and we > could be using more than just + ... we could also convert the other > way and want to use string concatenation - in that case, numbers > should auto-convert to strings - again, locale problem. I'm not aware of any application that auto-converts to text in this case. '+' is always used as a numerical operator. We should not create artificial and even more far fetched "examples" that just confuse discussion. > 2. =3+A7, where A7 holds a text. > > The important thing to keep in mind here is that 99% of users will > never encounter any of these, so we don't really need to worry about > them. This is simply not true. Experience shows that quite an amount of users is hit by this. > That leaves us with the "power users" and "geeks", and for them, > I think it would be preferred to keep the options there. Absolutely not my point of view. This isn't even for geeky power users, it's a result of a combination of a legacy "computations were always done in the en_US or C locale" and supporting the sloppiness of unexperienced users, which in itself isn't bad, but in this case just created problems in the long run. > As for locale conversions, there are three options. > A. ignore the problem and hope for the best. Which is what many spreadsheets do. To my opinion unacceptable. > B. such number->text conversions can only happen if the number is in > C-locale (remember, we're talking power users here, so regular users > won't have to do this) We are not talking about power users here. We're talking about the average spreadsheet user accidentally having input some number as text. Power users know why text doesn't calculate and what to do if it doesn't. > C. number-text conversions first try C-locale, and then try user's > locale. That way, if people want locale-agnostic documents, they can > stick to using C-locale in formulas - not a problem for programmers, > and others won't use this much anyway. Sigh.. again, you are basing this on wrong assumptions. > I personally am for C, maybe B. > Possibly add two conversion functions, VALUE and VALUEC (or a similar > name), with one converting only from C-locale, the other one taking > locale into account. That way, people who want it simple and flexible > will have it, people who want it to work reliably across locales will > get the option as well. VALUE() always worked according to the current locale. If we made up another function it would be VALUEL(v;locale), no need to restrict that to a C-locale-only function. > Note that we have this problem also in reverse, number->string > conversions. With the TEXT() function and the '&' operator and CONCATENATE(), yes. Eike -- Automatic string conversions considered dangerous. They are the GOTO statements of spreadsheets. --Robert Weir on the OpenDocument formula subcommitee's list.
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]