A-functions

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

Greetings, everyone !

Not sure if this was already discussed or not, but I think not, as I
don't recall seeing this problem raised so far.

I am not sure what to do about A-functions. By A-formulas, I mean the
functions that are derived from certain functions, and have A appended
- such as SUMA, AVERAGEA, COUNTA, and so on.
These are supposed to operate similarly to the non-A versions, with
some differences. Problem is, each spreadsheet has different
differences.

My research amongst spreadsheets at my disposal yielded the following results:
- OpenOffice2: non-A versions convert text values to numbers if
possible, and produce an error if conversion failed. However, if the
conversion fails for a text that's a part of a range, it gets
converted to 0 - hence, error only given for inline text constants.
A-versions treat all text values as 0.
- Gnumeric: Non-A versions completely ignore text -and- logical
values. A versions treat False as 0, True as 1, all text values as 0.
- KSpread: Non-A versions completely ignore text -and- logical values.
A versions treat False as 0, True as 1, text values get converted to
0. If a text cannot be converted, it equals 0.
- Excel 97: Non-A versions ignore text -and- logical values. However,
inline text values always get auto-converted to numbers, if possible.
If not, error is given. In array references, text values are ignored.
A-versions treat text values from references as 0, and also
auto-convert inline text values to numbers, if possible. If not, error
is given as well (even though text should be considered 0).

Most spreadsheets behave in a relatively sane way, with the exception
of OpenOffice's differentation of inline/non-inline text values (Eike
agreed that this should change, if I recall correctly), and Excel's
behaviour, which feels rather broken. Might have been changed in newer
Excel versions - don't have those available.

And what would I propose ?
Non-A functions:
- Numbers treated normally.
- Logical values ignored. (most spreadsheets do it that way, I
personally don't care)
- Text values always ignored.
- Empty values always ignored.

"ignored" means, omitting that value totally will yield exactly the
samer result.

A-functions:
- Numbers treated normally.
- Empty values always ignored.
- Logical values: true is 1, false is 0.
- Text values: implementation dependent. If the spreadsheet
auto-converts text values to numbers, it should do that here. If not,
text values should get converted to 0. If the auto-conversion fails,
error value should be propagated.

- COUNT and COUNTA are exceptions, as they just count values - COUNT
counts number values and nothing else. COUNTA also counts logical and
text values.

Also, the functions should accept arbitrary number of parameters, and
automatically expand ranges (so you wan do things like,
AVERAGEA(5.4;788;A1:G8).

Opinions ?

/ Tomas