Re: [office-formula] Our next adventure: Types and conversions

[Eike Rathke <erack@sun.com>]

Hi David,

On Sun, May 21, 2006 at 22:02:11 -0400, David A. Wheeler wrote:

> A. Logical is REQUIRED to be distinct from Number.
>     Inconsistent with OOo, Lotus 1-2-3, & many others.
> B. Logical is REQUIRED to be the same as Number:
>     Inconsistent with Excel, Gnumeric.
> C. "Logical" used as a notional type (so that we can easily
>    identify functions that take/return logicals), but we
>    explicitly permit EITHER of the above.
> I recommend "C".  If implementations can co-exist
> with this variance, I think we can too.

The problem is, applications can often coexist, but sometimes can't if
Logical is contained in a referred range that produces a list of numeric
values, e.g. =SUM(A1:A3), where Excel and Gnumeric do not sum a Logical
value contained in A2. They also don't sum logical values of result
arrays. They do sum however a multiplication of such array by one.
Example:

A1: 1
A2: =1=1
A3: 1

Formula             Result
=SUM(A1:A3)         2
{=SUM(A1:A3=1)}     0
{=SUM((A1:A3=1)*1)} 2

In OOoCalc all three formulas produce a result of 3.

We could define Logical distinct from Number, but explicitly define
implicit conversions, so that functions produce corresponding results:

A Logical always implicitly converts to a Number, a Number does not
implicitly convert to a Logical. Which results in
ISLOGICAL(Logical)==TRUE and ISNUMBER(Logical)==TRUE,
ISLOGICAL(Number)==FALSE and ISNUMBER(Number)==TRUE.

With this, it would also be possible for a user to distinguish between
the two different approaches if necessary, since for the Excel/Gnumeric
behavior ISNUMBER(Logical)==FALSE.


> Next up: how do they interact? In particular: if a function
> expects a Number but gets a Text value, then what?
> A. Text auto-converted to Number.  Excel & Gnumeric
>     do this.  There is a BIG PROBLEM here involving
>     locale... a conversion can work in one locale, & fail
>     in another.

Furthermore there is the already mentioned difference between
=SUM(A1:A2) and =A1+A2.

>     A1. Conversions usually work using the
>     "current locale" this is a problem -- the SAME spreadsheet
>     would work in one place and fail in another.  Yes, many
>     implementations (e.g., Excel) really do this.
>     A2. An alternative would be to save the
>     locale of a spreadsheet on creation, and ALWAYS use that
>     "sheet locale" on an automatic conversion. But note that
>     nobody does that, so we have the risk of invention
>     without experience.

This is not really viable. Despite from yet unforseen obstacles there
may be cases where the creation of a document was done in a locale that
is unknown to the application loading the document.

> 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,
>     since it would force spreadsheet authors to notice.
>     I know of no one who does this.

Me neither, but still it would be safest..

> E. Allow some set of the above.  Spreadsheet users could
>     add VALUE() calls where they wanted conversions.
>     One problem: VALUE() doesn't have a fixed locale, it
>     uses the current locale (usually).  We could modify the
>     definition of VALUE, or create VALUEL(v;locale).
>     (Creating a new function as a standards invention
>     seems less evil to me, though it's still not ideal.)

We should not modify the definition of VALUE() for this. If at all,
a VALUEL() function would be a clean approach, but then again, if a user
would use that, why wouldn't he correct the text to a number at first
hand? Which of course does not speak against the availability of
VALUEL() function for general purposes.

> I recommend (E) with "all of the above except A2".
> I'm not sure we want to create VALUEL(), though
> it'd be nice to have.

You mean, to include the case of A1? How would that fit together? Doing
so would keep the uncertainty of how a spreadsheet would calculate in
another application, whereas a clean definition of "text doesn't
calculate" allows export to any application.

  Eike