Re: [office-formula] Semantics

[Eike Rathke <erack@sun.com>]

Hi Tomas,

On Wed, Mar 01, 2006 at 09:45:01 +0100, Tomas Mecir wrote:

> > > First of all, my fundamental principle: as far as the user is
> > > concerned, there are no datatypes. Everything automatically gets
> > > converted to what is expected.
> > This is exactly the hell in which the approach of the one big player
> > brought us..
> 
> Really ? What kind of hell ?

The hell of strings being sometimes interpreted as numbers and sometimes
not. The hell of locale 

> I don't see any problem with this approach - after all all those
> "high-level" programming panguages do the same ...

Which ones do what?

> Or, do you see any reason why strings shouldn't be treated as numbers
> for values like "3", other than performance reasons ?

My favorite examples: The values of "12'345.67" and "12 345,67" and
"一二、三四五．六七" and "１２，３４５．６７" are exactly what?


> > Now consider that you pass the document to a colleague who works with
> > a different locale. Maybe it breaks.
> 
> 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.

> Is it even -possible- to design things so that they're
> locale-independent, without majorly limiting functionality ?

Nothing limits functionality if you strictly acted on numbers only in
numeric context. The user designing the sheet would notice from the very
beginning that something doesn't calculate the way he thought it would,
there'd be no surprise when somebody else working with a different
locale loaded the document. By allowing the
guess-what-it-could-be-but-maybe-it's-wrong mechanisms you limit
functionality.

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


> > > I realize this may create problems, but it follows my fundamental
> > > principle of no datatypes.
> > Well, it's your decision, but then you'd have to write COUNTA to the
> > file format instead of COUNT. And you wouldn't be able to correctly read
> > a file that uses COUNT.
> 
> If we decide that the fileformat should distinguish between COUNT and
> COUNTA, then yeah. You raise some valid arguments here, but I'm not
> convinced, as both solutions seem to have problems. Either restricted
> functionality, or problems with locale. Which is more important not to
> have ? Who can decide that ?

What do the trillion documents in the wild use? There's your answer.


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

> And see, another inconsistency. =(3+"Hi")/2 should raise an error,
> AVERAGEA on two values, "Hi" and 3, will yield 1.5 ...

Yes, that's how AVERAGEA is defined. (3+"Hi") is undefined. Note that
AVERAGEA and other *A are often used where measurement readings are
missing and a comment is placed instead. So it may make sense to
evaluate these as zero in this context. If the comments are not to be
evaluated you use AVERAGE instead. It's as simple as that.

  Eike