Regina, Thanks for your comment to the ODF TC. I think that is the way to address spreadsheet function iwssues.
I also agree that the grounding should be on ODF OpenFormula, since that is what the documents created by AOOi declare the formulas to be. Now is a good time to be creating Errata and advisories on problems like this. - Dennis -----Original Message----- From: Regina Henschel [mailto:[email protected]] Sent: Thursday, July 05, 2012 07:55 To: [email protected] Subject: Re: [Calc][Discuss]Interoperability with Excel for SUMIF, COUNTIF, COUNTBLANK, DCOUNT, DSUM, ... with empty cells and "" and ="" empty string criteria. 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 of other functions already. Kind regards Regina Lei Wang schrieb: > @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 > <[email protected]>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:[email protected]] >> Sent: Monday, July 02, 2012 07:39 >> To: [email protected] >> 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. >> >> [ ... ] >> >> >> > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
