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

[Richard Kernick <yingtong@bluebottle.com>]

David A. Wheeler wrote:

First: Which types?
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.
  

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.

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.
  

I don't accept that it is only geeks who end up with =A1+3 (where A1="3") and expect 6. I my organisation deal with a lot of spreadsheet imported from text files spat out by customers' systems. These often end up with conversion issues. Computers should just work! They shouldn't put people through unnecessary pain or wearyingly steep learning curves. Equally, they should ensure the user knows what is going on to ensure they are getting the results they are seeking. 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?

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

--- Richard Kernick