Re: [office-formula] A-functions

[Eike Rathke <erack@sun.com>]

Hi Tomas,

On Thu, Sep 07, 2006 at 11:49:48 +0200, Tomas Mecir wrote:

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

Not exactly. The non-A-versions taking NumberSequences always ignore
non-inline text, it is not converted to 0 as may be seen with the
AVERAGE function, while inline-text produces an error. The A-versions
convert any text to 0, whether inline or non-inline.

> 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),

Yep. Maybe with the exception of date values that should be possible to
be given in the full ISO 8601 form yyyy-mm-dd, e.g. "2006-09-07", just
for user convenience.

> and Excel's
> behaviour, which feels rather broken. Might have been changed in newer
> Excel versions - don't have those available.

Seems to be the same in Excel 12, at least in some common AVERAGE[A]
test cases.

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

Fine, this is what OOoCalc does. Except that it doesn't have distinct
Logical values.

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

Also fine. About auto-conversion in general see .sig ;-)

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

So why is that an exception then? It is congruent with the behavior
above, or did I miss anything?

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

This is standard behavior for functions taking NumberSequences.

  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.