User-Agent: Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.110 Safari/537.36 OPR/36.0.2130.65 Build Identifier: Version: 5.1.1.3 Build ID: 89f508ef3ecebd2cfb8e1def0f0ba9a803b88a6d CPU Threads: 4; OS Version: Windows 6.1; UI Render: default; Locale: pl-PL (pl_PL) Initially the spreadsheet is empty, in particular column D is blank. (Semicolon used in formulas below due to the local settings in my country.) 1. I enter formula =COUNTIF(D1:D5;D1) in A1, the result computed is 0 (consistent with other spreadsheets, including Excel and OpenOffice). 2. I enter formula {=COUNTIF(D1:D5;D1)} in B1, as an array formula (Ctrl-Shift-Enter), the result computed is 0 (consistent with other spreadsheets, including Excel and OpenOffice). 3. I enter formula {=COUNTIF(D1:D5;D2)} in B2, as an array formula (Ctrl-Shift-Enter), the result computed is 0 (consistent with other spreadsheets, including Excel and OpenOffice). 4. I enter formula {=COUNTIF(D1:D5;D1:D2)} in C1:C2, as an array formula (Ctrl-Shift-Enter). I expect (by 2 and 3 above and the semantics of array formulas) to get a column 0;0. However, I get a column 5;5. This is incorrect, inconsistent with 2 and 3 above, incompatible with other spreadsheets, including Excel and OpenOffice. Reproducible: Always Steps to Reproduce: 1. Strat with blank spreadsheet 2. Enter {=COUNTIF(D1:D5;D1:D2)} in C1:C2, as an array formula (Ctrl-Shift-Enter). 3. Actual Results: 5 5 Expected Results: 0 0 [Information automatically included from LibreOffice] Locale: en-US Module: SpreadsheetDocument [Information guessed from browser] OS: Windows (All) OS is 64bit: no Reset User Profile?No
Reproducible with 5.0.5.2 and dev 5.2.0. When 2nd argument of COUNTIF is an array and this array contains a blank cell, COUNTIF counts blanks. Enter anything in D1 and the result is 1;4 (should be 1;0).
(Same version as initailly) COUNTIFS, SUMIF, SUMIFS, AVERAGEIFS display the same behaviour. Surprisingly enough, AVERAGEIF behaves as it is supposed to, ignoring rows with blanks even if the criteria range contains blanks. However, it does not work correctly, either. If the context is as follows: Column A: blank;1 Column B: 1;2 then the formulas =AVERAGEIF(B1:B2;A1:A2;A1) =AVERAGEIF(B1:B2;A1:A2;A2) compute #DIV/0!;1 while {=AVERAGEIF(B1:B2;A1:A2;A1:A2)} computes #DIV/0!; #DIV/0!
Taking.
(In reply to Jerzy Tyszkiewicz from comment #2) > However, it does not work correctly, either. If the context is as follows: > Column A: blank;1 > Column B: 1;2 > > then the formulas > =AVERAGEIF(B1:B2;A1:A2;A1) > =AVERAGEIF(B1:B2;A1:A2;A2) > compute > #DIV/0!;1 > while > {=AVERAGEIF(B1:B2;A1:A2;A1:A2)} > computes > #DIV/0!; #DIV/0! There's nothing wrong with these. It may not be immediately obvious, but * assuming that =AVERAGEIF(B1:B2;A1:A2;A1) is placed in C1 and =AVERAGEIF(B1:B2;A1:A2;A2) is placed in C2 then for the scalar Criteria (2nd parameter) the argument to match against B1:B2 is taken from the implicit intersection of the range A1:A2 and the formula cell position, hence the intersection for C1 is A1 and the intersection for C2 is A2. * The range to average (3rd parameter) *starts* at A1 for C1 and A2 for C2, the actual dimension is taken from the range to match the criteria against. * For the array/matrix case {=AVERAGEIF(B1:B2;A1:A2;A1:A2)} in the first iteration the criteria in A1 (empty) is matched against B1:B2 and for the matching positions (none) the average built over A1:A2 (no value => #DIV/0!) as result in row 1. * For the second iteration the criteria in A2 (1) is matched against B1:B2 with TRUE for B1 in the first row and FALSE for B2 in the second row. For the TRUE matching position row 1 in A1:A2 there is no value => #DIV/0! as result in row 2.
(In reply to Eike Rathke from comment #4) OK, you are right.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=33090865c494618f4e528bf5a10aae8c4fc443d1 Resolves: tdf#99291 empty array element should not match empty cell It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/30451 for 5-2
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=01875c4514c777a43ee18014d9b0cb3f34eebbba unit test for tdf#99291, empty array elements match 0.0, not empty It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=bd47f43ce56639c891beda40d9070591b8df5ccf&h=libreoffice-5-2 Resolves: tdf#99291 empty array element should not match empty cell It will be available in 5.2.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.