+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:[email protected]]
Sent: Monday, July 02, 2012 02:54
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,
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: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
