Re: [office-formula] A-functions

["David A. Wheeler" <dwheeler@dwheeler.com>]

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.

Eike Rathke:
> 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.

I don't have Excel handy this instant, but I'm pretty sure taht for it, inline text
as a parameter is converted to a number (where possible; I suspect it's
an error if it can't, not sure).  Walkenbach goes into this in great detail for SUM.

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

Remember that there are two cases: in-line parameters, and
values contained in an array/range.

> > Non-A functions:

(I presume here you mean the values inside a range)

> > - Numbers treated normally.
> > - Logical values ignored. (most spreadsheets do it that way, I
> > personally don't care)

Well, when they are distinct types.  So this is really implementation-defined.

> > - Text values always ignored.
> > - Empty values always ignored.


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

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

The "A" is really Excel's naming convetion for "these are Lotus 1-2-3's
semantics", so the "A" doesn't imply equal semantics.  Unfortunately.

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

Yes.  The way to get that in this spec is to say that it takes a LIST of Sequences.
E.G., SUM and friends take this:
  { NumberSequence }+
So SUM( [.A1:.B3]; 5; TRUE() ) is passed a list of 3 NumberSequences:
 [.A1:B3], 5, and TRUE()
The rules for converting a NumberSequence say that [.A1:.B3] is
converted into a list of the numbers in the range, 5 is converted into one value (5),
and TRUE() is converted into one value (1).

AVERAGEA doesn't follow the same rules, and it looks like its funny rules
are shared by others.  We may need another type (other than NumberSequence)
to capture its oddities clearly.

--- David A. Wheeler