Re: [office-formula] A-functions

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

Tomas Mecir wrote:
> On 9/7/06, David A. Wheeler <dwheeler@dwheeler.com> wrote:
>> Remember that there are two cases: in-line parameters, and
>> values contained in an array/range.
> 
> Not really. The behaviour should be the same in both cases, unless
> there is a very good reason for it to be otherwise. I am not aware of
> one in this case.
 >
> I know that some spreadsheets behave differently in these cases, Excel
> and OOo at least, but Eike said that this difference should be removed
> in future versions of OOo, and Excel's differences are too weird to
> base a spec on anyway :)

Which differences do you mean?

We need to be very careful that our specification can handle current
documents.  Existing documents depend on all sorts of weird semantics,
and if our spec causes them to subtly and quietly give wrong answers, 
it's NOT okay.  Internal simplicity is a good goal, and we should
strive for it, but the ability to transition TO this spec will often
trump simplicity.

In particular, I think common functions like SUM will NEED to
have differences for in-line parameters vs. values in ranges.
These semantics are well-documented, and broadly expected.
At the very least, implementations need to be ALLOWED to implement
these semantics, because I believe many documents depend on them.
Books like Walkenbach's actively ENCOURAGE - almost COMMAND -
users to depend on these odd semantics :-(.

Remember, Excel added some "trivial" differences in semantics... they
then add to add new functions like COUNTA that were "almost" the same
as COUNT, but had Lotus 1-2-3 semantics, because otherwise no one was
willing to switch their spreadsheets to Excel.  We need to be
very sensitive to the needs of those who are transitioning with
their existing documents.

In the specific case of in-line vs. in-a-range, there are actually
rational reasons for how SUM() etc. typically work.
If something is DIRECTLY listed as a parameter in a SUM list,
then it's reasonable to think
that the user expected it to be added directly.  But if it's a range,
the usual notion is to "only consider the numbers in the range".

--- David A. Wheeler