Re: [office-formula] SUMIF, COUNTIF, COUNTBLANK, DCOUNT, DSUM,... with empty cells and "" and ="" e

[Eike Rathke <erack@sun.com>]

Hi robert_weir,

On Tuesday, 2007-01-02 10:24:25 -0500, robert_weir@us.ibm.com wrote:

> What was the argument against 1B -- self-consistency?

The problem is, as always, interoperability with applications that
implement the Excel behavior.

> I'm not in favor of departing from Excel's behavior for capricious 
> reasons, but if we can get greater consistency and simplicity at the same 
> time by make small deviations, then I'd certainly consider it, even if it 
> requires more work for existing implementations to consume & translate 
> Excel files.

Most times when implementing a different behavior there is no bullet
proof method to "translate" an Excel file behavior, especially in the
context of roundtrips from/to that alien file format.

Calc's DSUM and DCOUNT behavior is clearly wrong for literal empty
string criteria and we should follow Excel there.

This leaves the discrepancy between DSUM/DCOUNT and SUMIF/COUNTIF, which
is understandable if we take into account that the D* functions are used
for data lookup and don't mimic the "an empty string compares equal to
an empty cell" behavior.

Excel's behavior with COUNTBLANK is somewhat ridiculous, counting
formula cells that return an empty string, after all they're not empty,
but there seems to be a difference between blank and empty and blank
that only they know, ISBLANK does not return TRUE on such cells. I tend
to not mimic that nonsense in order to have COUNTBLANK to be consistent
with ISBLANK.

On the other hand the Excel behavior of COUNTIF in the case of comparing
a literal empty string with empty cells is consistent with its SUMIF and
COUNTBLANK, as long as a cell range is evaluated and not single cells.

In case of SUMIF with a literal empty string as criteria we should
follow Excel and compare an empty cell as equal. But: in Excel
SUMIF(range1;x;range2), if x is 0 or an empty cell compared against an
empty cell, is not the same as SUM((range1=x)*range2) in array context,
what one could expect.

So, if viewed from different angels the Excel behavior somehow does make
sense and somehow does not. And of course the fine MOOXML document
doesn't tell us anything except the COUNTBLANK behavior.

  Eike

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