incubator-ooo-users 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 11:39:31 GMT
+1 with primary concern being compatibility with OpenFormula and ODF 1.2.

The relevant documents are here <http://docs.oasis-open.org/office/v1.2/os/>.  The OpenFormula
specification is in Part 2, OpenDocument-v1.2-os-part2.*, where the .odf and .pdf are the
most usable.

It appears that there is ambiguity with regard to comparisons when the type of a scalar is
not clear (e.g., a comparison where a cell is empty and it is not clear whether it should
be understood as a number (0) or a string ("") in processing the condition.  For some comparison
operations, there are implementation-dependent cases for mismatched types.

On the other hand, OpenFormula section 4.11.8 Criterion may cover all the cases in question
for SUMIF and COUNTIF. I've added some cases to check in a comment on the old issue.

 - Dennis

-----Original Message-----
From: Oliver-Rainer Wittmann [mailto:orwittmann@googlemail.com] 
Sent: Monday, July 02, 2012 02:54
To: ooo-users@incubator.apache.org
Subject: Re: [Calc][Discuss]Interoperability with Excel for SUMIF, COUNTIF, COUNTBLANK, DCOUNT,
DSUM, ... with empty cells and "" and ="" empty string criteria.

Hi,

On 02.07.2012 10: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.
>

first, I am not familar with spreadsheet formulas.

I agree that interoperability with Microsoft Excel is important. But also 
important and from my point of view even more important is the conformance to 
ODF 1.2.
I do not know, if the needed changes would cause an unconformance with ODF 1.2. 
If yes, things will be complicated.

just my thoughts from an ODF perspective.


Best regards, Oliver.

---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org


Mime
View raw message