Created attachment 43326 [details] Example sheet with COUNTIF functions giving inconsistent results marked in red COUNTIF should count empty cells belonging to an array both as zero (0) and as empty string (""). Therefore COUNTIF(<array>;0) and COUNTIF(<array>;"") should yield the same results if the cells of <array> are empty (e.g. <array> {={||}}). However, the upper left cell of the array is not counted as zero but is counted as empty string, thus the results of both functions differ by one. This bug apparently already existed in OpenOffice for quite some time. It has been filed there as Issue 108991 [http://qa.openoffice.org/issues/show_bug.cgi?id=108991] but was never confirmed.
This may be related to bug 40110.
[This is an automated message.] This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it started right out as NEW without ever being explicitly confirmed. The bug is changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases. Details on how to test the 3.5.0 beta1 can be found at: http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1 more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
(In reply to comment #2) The problem still exists in LibO 3.5.0 beta2.
I did further testing on Win7-32, Win7-64, MacOS10.6 both on x86 and x64-32 platforms and the bug was reproducible everywhere with LibO 3.5.1. Thus the updates in the header information.
The problem still exists in LibO 3.6.0 beta2.
I was told that the "version" field should hold the first confirmed version with a bug, not the most recent one. There I reverted it to 3.3.0 (the bug was already present in OpenOffice).
The problem still exists in LibO 4.0.0.2 rc.
The problem is still present in LibO 4.0.2.2. "Version" field was reverted to "3.3.0 release" (see comment #6 on this).
@Tobias Lippert: Are you working on this bug? If yes, could you please set the status to ASSIGNED? If no, do you mind if I take it to fix? No guarantees ;-)
Hello Winfried, Please go ahead and take the bug. Tobias
Tobias, adding you to cc, so you can still monitor the bug.
Created attachment 110057 [details] Example sheet with COUNTIF functions giving inconsistent results marked in red Added some more examples of unexpected behaviour, all in the case of an array with empty elements as data range.
Created attachment 110120 [details] Example sheet with functions giving inconstent results The attachment shows that COUNTIF is not inconsistent, but that the range being a simple range or an array determines the inconsistency. Functions like ISTEXT and even direct comparisons are quite consistent. The array notation makes that evaluation of the cell is performed in a different way.
@Eike: Do you recognise what's in comment #13 and attachment 110120 [details] ? If so, could you give me code or class pointer? If not, I'll simply keep digging ;-)
The behaviour is caused by the fact that the first array cell is filled differently than the rest of the array cells: <table:table-cell table:number-matrix-columns-spanned="1" table:number-matrix-rows-spanned="3" table:formula="of:={||}"> <text:p/> </table:table-cell> </table:table-row> <table:table-row table:style-name="ro1"> <table:table-cell office:value-type="float" office:value="0" calcext:value-type="float"> <text:p/> </table:table-cell> </table:table-row> <table:table-row table:style-name="ro1"> <table:table-cell office:value-type="float" office:value="0" calcext:value-type="float"> <text:p/> </table:table-cell> The first array cell is considered to be a formula (containing the definition of the array), and the other cells are numeric cells (empty, leading to a value of 0). That explains the behaviour of COUNTIF, (cell=0), ISTEXT and ISNONTEXT. I still want to investigate the behaviour of ISBLANK and ISNUMBER. Knowing the cause does not mean I know the solution yet; fixing this problem must not break other functionalities.
Created attachment 110394 [details] Example sheet with functions giving inconstent results Expanded the sheet with some more examples. It seems that empty cells produce inconsistent results, too (cells F39-F45). Most inconsistent array results can be explained with the way an array is stored (see comment #15), but not all (cells L27-L29).
Unfortunately, I see no decent way to correct this in a reasonable time. Therefore I reverted the status to NEW. Hopefully, someone will fix this before I find the solution.
Found new possibilities as there are some relations with bug 85258.
Problem resolved with the bug fix for bug 39316: http://cgit.freedesktop.org/libreoffice/core/commit/?id=eccbc97c7c224269fe261b8924e7866c3758ec91 Currently in master and 4-4, pending review for 4-3.
Removing comma from Whiteboard (please use a space to delimit values in this field) https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Whiteboard#Getting_Started [NinjaEdit]