incubator-ooo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dennis E. Hamilton" <dennis.hamil...@acm.org>
Subject RE: [Calc][Discuss]Interoperability with Excel for SUMIF, COUNTIF, COUNTBLANK, DCOUNT, DSUM, ... with empty cells and "" and ="" empty string criteria.
Date Mon, 02 Jul 2012 17:14:12 GMT
@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
View raw message