Download it now!
Bug 99291 - Empty array element should not match empty cell; SUMIF, AVERAGEIF, COUNTIF, SUMIFS, AVERAGEIFS and COUNTIFS.
Summary: Empty array element should not match empty cell; SUMIF, AVERAGEIF, COUNTIF, S...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:5.3.0 target:5.2.4
Keywords:
Depends on:
Blocks:
 
Reported: 2016-04-14 10:23 UTC by Jerzy Tyszkiewicz
Modified: 2016-11-03 21:33 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Jerzy Tyszkiewicz 2016-04-14 10:23:19 UTC
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
Comment 1 GerardF 2016-04-14 14:11:40 UTC
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).
Comment 2 Jerzy Tyszkiewicz 2016-04-14 16:36:28 UTC
(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!
Comment 3 Eike Rathke 2016-10-31 17:57:08 UTC
Taking.
Comment 4 Eike Rathke 2016-10-31 21:19:39 UTC
(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.
Comment 5 Jerzy Tyszkiewicz 2016-10-31 22:34:16 UTC
(In reply to Eike Rathke from comment #4)
OK, you are right.
Comment 6 Commit Notification 2016-10-31 22:56:31 UTC
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.
Comment 7 Eike Rathke 2016-10-31 23:17:09 UTC
Pending review https://gerrit.libreoffice.org/30451 for 5-2
Comment 8 Commit Notification 2016-11-02 15:18:04 UTC
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.
Comment 9 Commit Notification 2016-11-03 21:33:20 UTC
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.