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

["David A. Wheeler" <dwheeler@dwheeler.com>]

I asked:
>> First: Which types?

Richard Kernick replied:
>I don't like leaving things so open, but I'm not sure I have a better
>solution than C, I want to give it more thought.

Yes, the big disadvantage of "C" is that it leaves things
more open.  In practice, what it means is that you cannot
be SURE that Logical is a distinguishable type, so
SUM(...) etc. may give different answers on different systems
if it sums over Logical results, and ROMAN() will work
differently on different systems (the latter is rather
a kludge anyway).  I suspect very few spreadsheets
actually REQUIRES more specific behavior, which is the saving
grace here, but that's why we need to discuss this head-on.

I also asked:
>>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.
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.
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.
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.)
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.

Richard Kernick replied:
>I don't accept that it is only geeks who end up with =A1+3 (where A1="3")
>and expect 6.... This is a difficult path to tread.
> It is important to always remember that we are talking about the file
>format not the UI.
>If we insist on type conversion in the file format that doesn't preclude
>implementations from doing implicit type conversions (A) or requiring
>explicit conversions; but they MUST save with explicit conversions. So
>file format has =VALUEL("3.5"; "GB")+3 and the UI can display to the user
>="3.5"+3, because it does A. Another implementation may display
>=VALUE("3.5")+3, because it doesn't. But they must save back to
>=VALUEL("3.5"; "GB")+3.
>I don't think we should ignore locale (and I don't think locale can be
>sheet wide - A2). The spreadsheet should calculate correctly, or tell the
>user that it may not. This is why some argue for D - at least that is
>deterministic.
>Implementations can either support the VALUEL(v;locale) function or, when
>loading the sheet and finding VALUEL functions for a locale other than
>the machine locale, warn the user that the sheet contains conversions
>from other locales. It could offer the user the choice to convert the
>formulae to the machine locale with the caveat that the results may
>differ; or it could lock the VALUEL and precedent cells so that the user
>can only change those parts of the sheet it can deal with. These are
>implementation issues. Ultimately implementations would support VALUEL
>and allow a mix of locales in one sheet - so as the sheet is passed
>around the globe it always computes correctly.
>Some solution along these lines, IMO, brings great flexibility, is
>forward looking and doesn't ignore the problem. Or have I missed
>something?

Hmm, this is yet another approach, one I hadn't accounted for
(or thought of). You're right, you could completely hide this via
functions, which might not be displayed on some systems (due to their semantics).

One challenge is that you may have ranges of numbers, and all functions
that take ranges must specify the difference too.  The traditional way has
been to append an "A" at the end of some function names; that won't work for all.

I would add that you probably want a "VALUE-like function, but don't show it
to me", since people will want a VALUE() that shows, too.

This might really be a good solution.  Can you post an expansion on this idea,
asap?


>Can this be applied to the use of the logical type? I haven't thought
>that through yet.

No idea.  Perhaps if we talk through text->number it'll become clearer.

--- David A. Wheeler