OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

[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 ?


> 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

> 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

> 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.


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]