Re: [office-formula] Semantics

[Eike Rathke <erack@sun.com>]

Hi Tomas,

On Wed, Mar 01, 2006 at 14:32:17 +0100, Tomas Mecir wrote:

> > The hell of strings being sometimes interpreted as numbers and sometimes
> > not. The hell of locale
> 
> Hmm... So, to sum it up, the major problem with allowing automated
> string->integer conversions is locale, right ?

Not only. It's not even defined _when_ to convert a string, let alone
how.

> But then, if we attempt
> to make everything locale-independent, we necessarily fail.

Only when interpreting strings on the fly.

> What if the user types in VALUE("12`345,67"), or any of the other
> examples you gave ?

He's left in the rain when changing locale. Or if he passed the document
to someone using another locale.

> What about DATEVALUE and similar functions ? Then the results
> still won't be the same on different locales, right ?

Sure. That's why using locale dependent strings as parameters is ugly
and a NoNo in professional spreadsheet design.

> So by allowing
> ="3"+3 to exist, we don't make locale-related problems bigger.

We increase it by one. We should decrease it instead, if possible.


> > Which ones do what?
> 
> Well things like perl or php don't have datatypes and convert
> on-the-fly, as needed ...

None of them does it locale dependent, AFAIK. All use '.' decimal
separator, and probably don't even know about a group AKA thousands
separator.


> > > How would you do these things, then ?
> > You better don't. Numbers are numbers, and text is text. User input is
> > parsed according to the input locale, after that text content shouldn't
> > be reparsed. If the user designs a document the way formulas concatenate
> > strings to form numbers that have to be parsed in numeric context it is
> > his duty to get it right not to use locale dependent delimiters or to
> > not spread the document to someone who works using a different locale.
> > Else it may break. You can't prevent that under all circumstances.
> 
> Like I say above, even simple VALUE can create these problems.

Yes. And you can't do anything against it, except pointing the user to
a possible problem when he uses such functionality. It's up to the user
not to get caught in a trap. But that's UI.


> > > I think OOo supports things like "3"+3 is 6, right ?
> > > This is locale-dependent as well ...
> >
> > And I wish it was never introduced. We might even remove it. But that's
> > just my personal opinion.
> 
> Well it MIGHT make sense, but like I say, we can restrict locale-based
> problems a bit that way, but never remove them.

Numeric strings as direct parameter don't make sense, except if date or
time to not loose formatting information. But then again user could also
use a cell for that. Times when cells were precious and rare are gone..


> > > > Bear in mind that AVERAGEA(range) does not convert strings to numbers,
> > > > it treats them as zero. This is a different functionality.
> > >
> > > Well in my opinion, it should attempt conversion, and treat them as
> > > zeroes only upon a failure.
> >
> > On failure it should generate an error instead. My opinion.
> 
> So I take it that string->number conversions are okay in functions
> with the A suffix ?

No, that's not what I said. Compare with Excel, which converts _all_
strings to 0 in AVERAGEA if in a cell reference, both "3" and "hi", but
interprets a string if passed directly, e.g. "3", but then again
generates an error if the direct string is "hi". Now _that's_ what
I call a mess and a hell of string conversion.

  Eike