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

["Tomas Mecir" <mecirt@gmail.com>]

On 5/22/06, David A. Wheeler <dwheeler@dwheeler.com> 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.

Well the only situation where this could produce different results
would be when someone tries to add/multiply/something logical values.
That can happen either in some "geeky" computations, or in situations
like having a column of True/False values and wanting to determine the
amount of True values - which has another possibilities too - so it
doesn't really matter. Hence, as far as I am concerned, it would be C
or B. Not A - if someone wants those "geeky" computations, let him
have them.

> Next up: how do they interact? In particular: if a function
> expects a Number but gets a Text value, then what?

Mmm, our long conversations with Eike are going to start again, it seems.

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

Well, my opinion is the same. C only adds inconsistency - why should
references be any different from constants ?
B and D are basically the same, but IMO they just restrict
functionality where this isn't necessary.

I personally am for A1. The primary thing to consider here would be,
who is going to be affected ?

First of all, this will NOT affect regular typing of numbers into
cells, because every spreadsheet automatically parses entered text, so
if a number  is entered, it will be parsed with the current locale,
and then stored in the file using something like the C-locale.

Hence, the only two cases where this will affect anything would be:
1. ="3"+3 (either of these could be a product of some function, and we
could be using more than just + ... we could also convert the other
way and want to use string concatenation - in that case, numbers
should auto-convert to strings - again, locale problem.
2. =3+A7, where A7 holds a text.

The important thing to keep in mind here is that 99% of users will
never encounter any of these, so we don't really need to worry about
them. That leaves us with the "power users" and "geeks", and for them,
I think it would be preferred to keep the options there.

As for locale conversions, there are three options.
A. ignore the problem and hope for the best.
B. such number->text conversions can only happen if the number is in
C-locale (remember, we're talking power users here, so regular users
won't have to do this)
C. number-text conversions first try C-locale, and then try user's
locale. That way, if people want locale-agnostic documents, they can
stick to using C-locale in formulas - not a problem for programmers,
and others won't use this much anyway.

I personally am for C, maybe B.
Possibly add two conversion functions, VALUE and VALUEC (or a similar
name), with one converting only from C-locale, the other one taking
locale into account. That way, people who want it simple and flexible
will have it, people who want it to work reliably across locales will
get the option as well.

Note that we have this problem also in reverse, number->string
conversions. These, however, are less dramatic as they will almost
always only affect visual output, and only rarely will anyone want to
use something that depends on having the number in a particular
locale. Hence, we can just state that number->string conversions
happen using user's locale, and a function is provided to convert
number->string using C-locale, or using some given formatting. That
should cover all use cases.

Hope I didn't forget anything.

/ Tomas