incubator-ooo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lei Wang <lei.wang.l...@gmail.com>
Subject Re: [Calc][Discuss]Interoperability with Excel for SUMIF, COUNTIF, COUNTBLANK, DCOUNT, DSUM, ... with empty cells and "" and ="" empty string criteria.
Date Thu, 05 Jul 2012 03:28:13 GMT
@Dennis,

I agree that conforming to ODFF is first goal. I add comments and
attachment in https://issues.apache.org/ooo/show_bug.cgi?id=65221
>From my understanding of ODFF, I give ODFF expect result for some formula
related with empty cell in the attachment(xls). If there is any mistake,
please comments.

Open this file in AOO3.4/LO3.5, 16 cases fail.
There are:

Criterion in COUNTIF/SUMIF
                           ODFF              AOO3.4/LO3.5
 criterion as "="     empty cell        empty string cell
 criterion as "<>"    not empty cell        not empty cell and not empty
string cell
 criterion as "<>0"    any cell except        any cell except 0, not
including empty cell
                             value 0, including
                              empty cell

Criterion in criteria for database formula, such as DSUM, DCOUNT
                              ODFF            AOO3.4/LO3.5
 criterion is empty    value 0            all cells
 cell
 criterion is empty    empty string        all cells
 string
 criterion as "="     empty cell        empty cell and empty string cell
 criterion as "<>"    not empty cell        not empty cell and not empty
string cell

Also AOO3.4/LO3.5 has self-consistent problem.
The different result for same criterion between COUNTIF/SUMIF and database
formulu(DCOUNT/DSUM). These criterion includes:
criterion is empty cell, criterion is empty string, criterion is "=",
criterion is "<>0"

While, open this file in Excel 2003, 8 cases fail.
There are:

Criterion in COUNTIF/SUMIF
                               ODFF                  Excel 2003
 criterion is ""          empty string cell    empty string cell and empty
cell
 criterion is empty    empty string cell    empty string cell and empty cell
 string

Criterion in criteria for database formula, such as DSUM, DCOUNT
                             ODFF            AOO3.4/LO3.5
 criterion is empty    value 0            all cells
 cell

Also Excel 2003 has self-consistent problem.
The different result for same criterion between COUNTIF/SUMIF and database
formulu(DCOUNT/DSUM). These criterion includes:
criterion is empty cell, criterion is empty string

It seems Excel has better conformance for these test cases.

Conform to ODFF is very important. Compatible with Excel is also very
important, especially for some common used Excel scenario, such as
COUNTIF(<refrange>, "") to count all cells displayed as empty. Do we have
any chance to modify ODFF to solve this Excel user problem?


On Tue, Jul 3, 2012 at 1:14 AM, Dennis E. Hamilton
<dennis.hamilton@acm.org>wrote:

> @Andrew,
>
> See latest comments on https://issues.apache.org/ooo/show_bug.cgi?id=65221
>
> I agree with orw that the first goal should be to align with OpenFormula,
> and that is specific about the use of Criteria in SUMIF, COUNTIF, etc., as
> you saw in section 4.11.8.
>
> In the case that there needs to be further clarification in OpenFormula,
> comments can be submitted to the ODF TC for Errata and/or improvements in
> the next edition, ODF 1.3.  OpenFormula was developed with cross-format
> interoperability in mind and Microsoft participation was helpful for that.
>
> User Guide documentation on these cases is probably also important.
>
>  - Dennis
>
> -----Original Message-----
> From: Andrew Pitonyak [mailto:andrew@pitonyak.org]
> Sent: Monday, July 02, 2012 07:39
> To: ooo-dev@incubator.apache.org
> Subject: Re: [Calc][Discuss]Interoperability with Excel for SUMIF,
> COUNTIF, COUNTBLANK, DCOUNT, DSUM, ... with empty cells and "" and =""
> empty string criteria.
>
> On 02.07.2012 04:50, Lei Wang wrote:
> > Hi all,
> >
> > There is an old bug i65221(
> > https://issues.apache.org/ooo/show_bug.cgi?id=65221). It is about
> > interoperability with Excel for SUMIF, COUNTIF, COUNTBLANK, DCOUNT,
> > DSUM,
> > ... with empty cells and "" and ="" empty string criteria.
> >
> [ ... ]
>
> I am surprised that the ODF standard does not dictate how this should
> be handled. I was under the impression that function behaviour in an ODF
> document was well defined. I believe that Robert Weir is far more aware
> of ODF type issues than I (since I have seen him mentioned by name on
> similar topics) - not that I intend to unfairly pull him into this
> discussion.
>
> In Version 1.2 of the part-2 for OpenFormula, I don't see it obviously
> spelled out, but I don't really have time to extensively pursue it.
>
> [ ... ]
>
> 4.7 Empty Cell
> An empty cell is neither zero nor the empty string, and an empty cell
> can be distinguished from cells containing values (including zero and
> the empty string). An empty cell is not the same as an Error, in
> particular, it is distinguishable from the Error #N/A (not available).
>
> Moving on to section 4.8........
>
> The definitions of specific operations and functions that allow
> references as operands and parameters stipulate any particular
> limitations there are on forms of references and how empty cells, when
> permitted, are interpreted.
>
> Now, on to 4.11.8, pretty clear here:
>
> 4.11.8 Criterion
> A criterion is a single cell Reference, Number or Text. It is used in
> comparisons with cell contents.
> A reference to an empty cell is interpreted as the numeric value 0.
> A matching expression can be:
> A Number or Logical value. A matching cell content equals the Number or
> Logical value.
> A value beginning with a comparator (<, <=, =, >, >=, <>). 6.4.9
> For =, if the value is empty it matches empty cells. 4.7
> For <>, if the value is empty it matches non-empty cells.
> For <>, if the value is not empty it matches any cell content except
> the value, including empty cells.
> Note: "=0" does not match empty cells.
> For = and <>, if the value is not empty and can not be interpreted as a
> Number type or one of its subtypes and the host-defined property
> HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL is true, comparison is
> against the entire cell contents, if false, comparison is against any
> subpart of the field that matches the criteria. For = and <>, if the
> value is not empty and can not be interpreted as a Number type or one of
> its subtypes 3.4 applies.
> Other Text value. If the host-defined property
> HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL is true, the comparison is
> against the entire cell contents, if false, comparison is against any
> subpart of the field that matches the criteria.
>
> [ ... ]
>
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message