Bug 121696 - [EDITING] Usage of "Inequality" criteria in function SUMIF creates wrong result
Summary: [EDITING] Usage of "Inequality" criteria in function SUMIF creates wrong result
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.1.3.2 release
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bibisectRequest, bisected
: 122321 (view as bug list)
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2018-11-24 16:35 UTC by Martin Lorscheid
Modified: 2018-12-25 15:42 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Example for SUMIF failure in combination with Inequality (10.86 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-11-24 16:39 UTC, Martin Lorscheid
Details
example excel 2016 (8.78 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-11-24 19:04 UTC, Oliver Brinzing
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Martin Lorscheid 2018-11-24 16:35:48 UTC
Description:
Starting with V. 6.1 the function SUMIF creates wrong results.
When using "<> 0" as criteria the function adds all content of Sum Range without checking the range content.
Problem occurs since switching from V. 6.0.6.2 (x64)to V. 6.1.2.1 (x64)

see example Example_Inequality.ods


Steps to Reproduce:
see Example_Inequality.ods

Actual Results:
see Example_Inequality.ods

Expected Results:
see Example_Inequality.ods


Reproducible: Always


User Profile Reset: No



Additional Info:
[Information automatically included from LibreOffice]
Locale: en-US
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Windows (All)
OS is 64bit: no
Comment 1 Martin Lorscheid 2018-11-24 16:39:14 UTC
Created attachment 147004 [details]
Example for SUMIF failure in combination with Inequality
Comment 2 Mike Kaganski 2018-11-24 18:02:05 UTC
And what happens if you put 0 to B5 and B6?
Comment 3 Oliver Brinzing 2018-11-24 18:56:56 UTC
attached file has been saved with lo 6.1.3.2

checked with new userprofiles:

- open attached file with lo 6.0.7.1
- cell C10 shows 63
- ctrl+shift+F9 (calculate all)
- cell C10 shows 13

- open attached file with lo 6.1.3.2
- cell C10 shows 63
- ctrl+shift+F9 (calculate all)
- now cell C10 shows 63

so something has changed

- open attached file with lo 6.0.7.1
- ctrl+shift+F9 (calculate all)
- save and close
- open with lo 6.1.3.2
- cell C10 shows 13
- ctrl+shift+F9 (calculate all)
- now cell C10 shows 63
Comment 4 Oliver Brinzing 2018-11-24 19:04:54 UTC
Created attachment 147007 [details]
example excel 2016

result with excel 2016 is 63
Comment 5 Oliver Brinzing 2018-11-24 19:38:09 UTC
code changed with:

Resolves: tdf#73081 *) empty <> not-empty is also a match if numeric
This simplifies to match empty for the same conditions whether
numeric or not, an empty string does not result in numeric 0 here.

https://gerrit.libreoffice.org/plugins/gitiles/core/+/88f10aed35c19cc43a88bbcd12b026cb32c0003f

*) tdf#73081
COUNTIF with criterium <>number does not count empty cells.
https://bugs.documentfoundation.org/show_bug.cgi?id=73081
Comment 6 Mike Kaganski 2018-11-25 07:30:07 UTC
(In reply to Oliver Brinzing from comment #5)

So, intended change => NOTABUG? Just wonder why a release note missing.
Comment 7 Oliver Brinzing 2018-11-25 08:39:38 UTC
the changed calulation started with lo 6.1.3.2,
lo 6.1.3.1 shows the previous behavior.

as mentioned above: opening a spreadsheet created with lo 6.1.3.2 in an older version now will change formula result afer a hard recalc.

the change was intended:
an empty cell and a cell with formula ="" are same now (treated as <> 0),
formula ="0" is 0.

the "[ ] Treat empty string as zero" compatibility option seems to have no influence. is this correct?

and the question is: which formula's are affected?

at least the release notes should mention the change and the documentaion 
should be adjusted.
Comment 8 Mike Kaganski 2018-11-26 06:27:09 UTC
(In reply to Oliver Brinzing from comment #7)
> the "[ ] Treat empty string as zero" compatibility option seems to have no
> influence. is this correct?

Citing bug 73081 comment 0:

> Notice the part "including empty cells".
> 
> Notice, that this is not about the problem, whether a zero-length string is
> to be considered as empty cell.

So clearly this is the case where empty cells and zero-length-strings cells can be treated differently.

> and the question is: which formula's are affected?

citing bug 73081 comment 14:

> So COUNTIF() is inconsistent and now Calc does the same.

Some proper formulas for current state:

=SUMPRODUCT(A2:A7;B2:B7<>"";B2:B7<>0)
=SUMIFS(A2:A7;B2:B7;"<>0";B2:B7;"<>")
Comment 9 Oliver Brinzing 2018-11-26 18:07:32 UTC
(In reply to Mike Kaganski from comment #8)

> =SUMPRODUCT(A2:A7;B2:B7<>"";B2:B7<>0)
will not work with excel (result is 0)

> =SUMIFS(A2:A7;B2:B7;"<>0";B2:B7;"<>")
will work with excel too (result 13)

can we assign this issue to eike and regina for review?
Comment 10 Mike Kaganski 2018-11-26 19:11:17 UTC
(In reply to Oliver Brinzing from comment #9)
> can we assign this issue to eike and regina for review?

I don't think we can "assign" the issue to anyone. But of course, we can ask, and actually I have CC'ed Eike already, in the hope that he will come with necessary clarifications, if he finds useful. And CCing Regina might be useful as well. I believe that the linked issue explains it rather extensively, though, so maybe the question is in the realm of Olivier?
Comment 11 Eike Rathke 2018-12-03 12:44:34 UTC
So yes, the "<>0" condition matches empty cells now, to comply with ODFF and interoperability with other spreadsheet implementations, see also comment 3 about Excel that does the same. The previous behaviour that excluded empty cells was wrong and a bug.

An empty cell is not a number 0 in this context.

See also ODFF 4.11.8 Criterion http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__434502_49873571
"For <>, if the value is not empty it matches any cell content except the value, including empty cells. "
Comment 12 himajin100000 2018-12-25 15:42:28 UTC
*** Bug 122321 has been marked as a duplicate of this bug. ***