Re: [office-formula] Semantics

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

On 2/28/06, David A. Wheeler <dwheeler@dwheeler.com> wrote:
> Generally spreadsheets agree on nearly all semantics anyway.
> If you SUM a range, EVERYBODY skips the text values in the range.

>    + KSpread 1.5-beta: Text and reference-to-text is taken, and conversion
>       attempted; if success, returns converted number, else returns 0.


These two together become tricky, if they're to be used differently
... If error is returned instead of 0 for failed attempts, THEN we get
a problem with SUM - if it's to behave the same, it will no longer
ignore text ... Also, because SUM will ignore text references, it
already does have the problems with 1 being typed as L ...
And the users probably use SUM more than +, so the problem with
typewriter-trained users is already present. If it's even important -
I don't think typewriters are still used much :)

Hence the problem with handling text values where numbers are expected
would be, what kind of inconsistency should there be ? Because it
seems like there always will be SOME inconsistency somewhere, due to
the way existing spreadsheets work ...

How I personally would like these things to be, at highest levels:

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.

- Numbers are numbers, treated as numbers. No problem here.
- Passing a number to a string function converts the number to a
string, using locale settings.
- Passing a string where number is expected, converts the string to
some number. 0 if failure. Yes, that's how I did it in KSpread (so I
may be biased here), and yes, we silently ignore errors, but at least
we're consistent.
- SUM is doing the same, and functions like AVG basically just use SUM.
- COUNT includes text and boolean and whatnot. It does not include empty values.
- basically, SUM/COUNT/... become what SUMA/COUNTA/... are now.
- remove SUMA, COUNTA, AVGA, ***A - or make'em just aliases. I realize
this may create problems, but it follows my fundamental principle of
no datatypes. Anything else may be easier for the geek population, but
confusing for anyone else. Imagine a Random User (TM), who has a
column full of text data and tries to put a count at the bottom;
=COUNT(range) is the obvious choice, right ? Only that it won't work.
Bad, in my opinion. All this ***A business is only in because
spreadsheets didn't auto-convert values as needed, thus a work-around
was necessary.

We, however, shouldn't need work-arounds.

/ Tomas