incubator-ooo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrew Pitonyak <and...@pitonyak.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 14:39:18 GMT
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.
>
> There is some discussion about it, but no conclusion. Some people 
> think
> Excel does not do well, so simply mimicking Excel behavior might not 
> be the
> best idea. Some people think self-consistency in Calc is more 
> important.
> Some people think compatibility with Excel is a huge concern.
>
> IMHO, compatible with Excel is very important. I suggest changing 
> these
> formulas behavior according to Excel. Following is my consideration
> 1)Most people use Excel, compatible with Excel is very important. 
> Excel
> does not show self-consistency in some scenarios, Calc doe not show
> self-consistency either.
> 2)Some frequently used function, such as calculating empty cells, can 
> not
> be implemented by Calc, while Excel can.
> 3)Although Excel does not show self-consistency in some scenarios. 
> These
> scenarios are all related with pure empty cell. Excel use following 
> rules
>   When empty cell is criteria, only cells with value 0 meet it. This 
> is
> true for COUNTIF, SUMIF. But not true for DSUM/DCOUNT/IF.
>   When an empty cell in a cell range, it can meet empty string 
> criteria.
> This is true for COUNTIF, SUMIF. But not true for DSUM/DCOUNT.


I believe that you should expand your area of concern..... For example, 
I recently found a spreadsheet created and used in another spreadsheet 
program, don't know off hand if it was a Microsoft variant or a 
WordPerfect variant. The document was opened into Calc using OOo for a 
few years. When the document was tried on LibreOffice, it failed. Why? 
Because a few of the "empty" cells contained a single space and LO threw 
an error rather than dealing with it the way that the other programs had 
dealt with it. In this case, the only thing that I can say 
authoritatively is that LO and AOO treated the file differently. I did 
not pursue why LO made the change. My only point is that if you desire 
to argue that the functions in AOO should be changed to match those in 
Excel, you should also verify the behaviour of general numerical methods 
and how they treat cells with a blank.

Off hand, I think that documenting the differences would be very 
valuable even if changes are not made to AOO.

Perhaps even more important than compatibility with Excel is that the 
software that supports ODF natively are consistent.

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.

The closest I see of say "SUMIF" is this:

The values returned may vary depending upon the 
HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or 
HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties. 3.4

I did check the host defined behaviors section and that is primarily 
related to things such as locale and case sensitivity.

Well, it does say this:

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.



In your opinion, are you asking that the methods are changed to match 
4.11.8 or so that they will not match 4.11.8?


Sorry, but I am very interested in this.

Andrew Pitonyak









Mime
View raw message