Make sure B2 is empty, that means, that it does not contain any content. Write the formula =B2 into cell B3 Write the formula =COUNTBLANK(B2:B3) into cell A1. Expected result: A1 shows number 1. Seen result: A1 shows number 2. The content of cell B3 is a formula, therefore it is not empty and may not be counted. The specification only allows the exception, that a cell, which contains a zero-length string, may be counted by COUNTBLANK. Apache OpenOffice, Gnumeric, and Excel does not count cell B3 and return 1 in the example given above. Suggestion: LibreOffice does not count cell which has a formula that refers an empty cell.
It is mandatory to adher to ISO/CEI 26300 norm, but this norm leaves an open implementation-defined on the subject. I support the suggestion of Regina, for : - facilitating the application port to/from other spreadsheets, - the consistency with "ISBLANK".
also for me this seems to be inconsistent if COUNTBLANK returns 2 but ISBLANK returns another result
Still reproduced with LO 4.3.2.2 - Ubuntu 12.04 x86 Not reproduced with LO 4.0.6.2 and below
7be7cf83087144563a18000acdae82c8fd6f4872 is the first bad commit commit 7be7cf83087144563a18000acdae82c8fd6f4872 Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com> Date: Mon Dec 10 00:13:53 2012 +0000 source-hash-d59024b652ccfaf7247da113ec36788fe260de74 commit d59024b652ccfaf7247da113ec36788fe260de74 Author: Michael Stahl <mstahl@redhat.com> AuthorDate: Thu Jul 12 22:18:43 2012 +0200 Commit: Michael Stahl <mstahl@redhat.com> CommitDate: Thu Jul 12 22:18:43 2012 +0200 warning C4101: unreferenced local variable Change-Id: I0648821a4d0c716371bb011df8cd9b21db79ccf5 :100644 100644 2e11de60e03551ff9cb9202630fabddffd9b4d03 13d66368624db1158439984d29b9b6b4e382edc1 M autogen.log :100644 100644 1aef3215ed700bc4e85fd12d18339c24ebf234a7 1c9624dd465ee0e59acd9c9dd05950b51be0c666 M ccache.log :100644 100644 f13bcc6696e46ed88900fa5b2390642319998036 b55a2bd9212e07b4042ebd0bb46515c8a4e76c04 M commitmsg :100644 100644 9d65d2f7c48177028e23641bc3eb0528844456f9 5270492409e164dc751ec11e4524f63b7739b35f M dev-install.log :100644 100644 9134f8edd2ee62e54d25821b3cc071e2aee24b5a f188090f0a4137ab411a3ff359ec440f0da4644c M make.log :040000 040000 e1e8f612101470e3ff2f6a53fc8c5e664103243f bb164bd44517d96b184ae0ecb1e8ccaf46ba0b4d M opt git bisect log # bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e # good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932 git bisect start 'latest' 'oldest' # bad: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb git bisect bad e02439a3d6297a1f5334fa558ddec5ef4212c574 # bad: [8f4aeaad2f65d656328a451154142bb82efa4327] source-hash-1885266f274575327cdeee9852945a3e91f32f15 git bisect bad 8f4aeaad2f65d656328a451154142bb82efa4327 # good: [369369915d3582924b3d01c9b01167268ed38f3b] source-hash-45295f3cdceb4c289553791071b5d7f4962d2ec4 git bisect good 369369915d3582924b3d01c9b01167268ed38f3b # good: [6fce03a944bf50e90cd31e2d559fe8705ccc993e] source-hash-47e4a33a6405eb1b5186027f55bd9cb99b0c1fe7 git bisect good 6fce03a944bf50e90cd31e2d559fe8705ccc993e # bad: [da317333e5675622f55c9dda17396c659af65320] source-hash-15af925c254f27046427de70a59011e2ac3d6bdb git bisect bad da317333e5675622f55c9dda17396c659af65320 # good: [daa21bbd8c7b50e2ca1c2cbed0e39f0e7b5a1cb2] source-hash-6b11a18071254a443c8fe7e7b0b1c95b0f9fd35e git bisect good daa21bbd8c7b50e2ca1c2cbed0e39f0e7b5a1cb2 # good: [a08143f4bae3d6658dd756b42b6f343298d1f48c] source-hash-b7822657fa67e7265d07f5852057e975e9efae0d git bisect good a08143f4bae3d6658dd756b42b6f343298d1f48c # good: [5255e1fbf1f3fa3ca61c4db3005940205577863c] source-hash-63c004fc3f0bc53ce888ef012f7993b9203a7503 git bisect good 5255e1fbf1f3fa3ca61c4db3005940205577863c # bad: [7be7cf83087144563a18000acdae82c8fd6f4872] source-hash-d59024b652ccfaf7247da113ec36788fe260de74 git bisect bad 7be7cf83087144563a18000acdae82c8fd6f4872 # good: [ad874a5319e9f68e6b3a974e44de838b8a0a82e1] source-hash-4b4ca8030285bd66526ff5bb2b6ea5a75a6c6bc7 git bisect good ad874a5319e9f68e6b3a974e44de838b8a0a82e1 # first bad commit: [7be7cf83087144563a18000acdae82c8fd6f4872] source-hash-d59024b652ccfaf7247da113ec36788fe260de74
Taking this.
Fixed on master: http://cgit.freedesktop.org/libreoffice/core/commit/?id=3e2bd1e4022e25b77bcc8eba5e02c1adc57008a1
4.3 backport request: https://gerrit.libreoffice.org/11993
I've written a unit test for this, so this corner case is covered.
Regina, could you check with Eike (and vise versa)? He says in the gerrit review that "Um.. what I meant was that ="" should not be counted as blank/empty cell at all, the previous revision of this patch introduced that for range references, and this revision now introduces it also for single references, this was not the case before and not an existing issue. This changes behaviour. However, ISBLANK() does not yield TRUE for ="" and COUNTBLANK() should match that." That contradicts with what you originally said which is "The specification only allows the exception, that a cell, which contains a zero-length string, may be counted by COUNTBLANK." Thanks.
Perhaps this is another item that should go to the calculation settings. Excel counts ="" as blank in COUNTBLANK at least.
Which probably was the reason to have that implementation-defined in ODFF. I agree that we should align with Excel and Gnumeric in the behavior, but changing it in a micro release might not be good. I suggest to * for 4.3 fix the reference to empty cell to not be counted, which the original bug report is about, and not touch the ="" behavior * for 4.4 introduce yet another compatibility calculation setting for empty string returns
Created attachment 108137 [details] Examples with blank cells and empty strings I agree with Eike. My report is not about handling empty strings, but about handling a reference to an empty cell. That can be fixed in 4.3. code line. Handling of empty strings should not be changed in a micro release and only with compatibility options. Empty strings are widely used and changing behavior must not break existing spreadsheets; especially as it is not an error but an implementation dependent behavior. That is not suitable for a micro release. The attached spreadsheet contains several test usages of ISBLANK and COUNTBLANK. [BTW: I have detected a similar bug for ISBLANK, issue will follow.]
Fair enough. I've abandoned my backport to 4.3.
Can this be closed as FIXED?
I have tested Version: 4.4.0.0.alpha1+ Build ID: b167d1127e1579468a74c4414e7f4f33d9d24122 TinderBox: Win-x86@51-TDF, Branch:MASTER, Time: 2014-11-02_03:04:44 The error, that references to empty cells are count, is fixed. But there has been introduced a new behavior, that empty strings are count. This was introduced without the compatibility option Eike mentioned. And when opening an older document saved by OpenOffic.org, this is recalculated to the new behavior, although the option "never recalculate" is selected. Without a proper compatibility option as document setting, and an automatic detection for older documents, counting cells with empty strings should not be implemented. In addition the help should mention counting empty strings, because it is an implementation dependent feature. When you decide to close this issue, a follow up issue for the compatibility of the new behavior is needed.
(In reply to Regina Henschel from comment #15) > I have tested Version: 4.4.0.0.alpha1+ > Build ID: b167d1127e1579468a74c4414e7f4f33d9d24122 > TinderBox: Win-x86@51-TDF, Branch:MASTER, Time: 2014-11-02_03:04:44 > > The error, that references to empty cells are count, is fixed. Then can we close this and create a new bug?
yes.
Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit]