# incubator-ooo-users mailing list archives

##### Site index · List index
Message view
Top
From Regina Henschel <rb.hensc...@t-online.de>
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 14:55:01 GMT
```Hi Lei Wang,

I think, that ODF itself is ambiguous and not clear and have written my
concerns to office-comment
https://lists.oasis-open.org/archives/office-comment/201207/maillist.html

I think AOO should always calculate as defined in ODF. If Excel
calculates another way, it is possible to define a new function with an
postfix _add and use it for exchange with Excel. That is done for a lot

Kind regards
Regina

Lei Wang schrieb:
> @Dennis,
>
> 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,
>
> 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(
>>> 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.
>>
>> [ ... ]
>>
>>
>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org