| Summary: | Empty array element should not match empty cell; SUMIF, AVERAGEIF, COUNTIF, SUMIFS, AVERAGEIFS and COUNTIFS. | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Jerzy Tyszkiewicz <jty> |
| Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | gerard.fargeot, jty |
| Priority: | medium | ||
| Version: | unspecified | ||
| Hardware: | All | ||
| OS: | All | ||
| Whiteboard: | target:5.3.0 target:5.2.4 | ||
| Crash report or crash signature: | Regression By: | ||
|
Description
Jerzy Tyszkiewicz
2016-04-14 10:23:19 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). (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. |