Bug 1219287 - Recent update broke array SUM()
Summary: Recent update broke array SUM()
Keywords:
Status: CLOSED NOTABUG
Alias: None
Product: Fedora
Classification: Fedora
Component: libreoffice
Version: 21
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: ---
Assignee: Eike Rathke
QA Contact: Fedora Extras Quality Assurance
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2015-05-07 00:00 UTC by David Woodhouse
Modified: 2015-06-01 22:14 UTC (History)
6 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2015-06-01 17:26:36 UTC


Attachments (Terms of Use)


Links
System ID Priority Status Summary Last Updated
Document Foundation 42481 None None None Never

Description David Woodhouse 2015-05-07 00:00:20 UTC
See test case in https://bugs.documentfoundation.org/show_bug.cgi?id=86978#c6
See formulæ in Sheet3.V3:V22.

Load the file with LibreOffice 4.3.2.2-5.fc21 as originally shipped, and observe that these fields have a correct sum of the payments in each tax year.

Load the same file with LibreOffice 4.3.7.2-3.fc21 and observe they all say #VALUE!

Comment 1 David Woodhouse 2015-05-07 00:00:53 UTC
The problem was introduced to F22 in a recent update too.

Comment 2 David Woodhouse 2015-05-07 10:23:10 UTC
FWIW I've managed to work around this for now by changing from

=SUM(Sheet1.$D$9:$D$500 * (Sheet1.$A$9:$A$500>=$U2) * (Sheet1.$A$9:$A$500<$U3))

to

=SUMIFS(Sheet1.$D$9:$D$500,Sheet1.$A$9:$A$500,">="&$U2,Sheet1.$A$9:$A$500,"<"&$U3)

... which is probably a better way to do it anyway, but this shouldn't have broken in a stable release.

Comment 3 David Tardon 2015-05-07 14:03:35 UTC
This changed due to fix for https://bugs.documentfoundation.org/show_bug.cgi?id=42481 . I doubt we want to revert that.

Comment 4 David Woodhouse 2015-05-07 15:42:09 UTC
That bug appears to talk about SUMPRODUCT(). It's not clear that they had any intention to change the behaviour of SUM().

(I cannot comment there yet because their new bugzilla is sending mail from a bogus address <bugzilla-daemon@bugs.documentfoundation.org> to which they do not accept bounces.)

Comment 5 David Tardon 2015-05-08 07:25:05 UTC
The bug that has been fixed was that arithmetic operators did not propagate errors if used on matrices. That means the change has been in *, not in SUMIFS or any other function.

Comment 6 Eike Rathke 2015-06-01 17:26:36 UTC
The root cause of the #VALUE! errors is that in Sheet1.D9:D500 the formulas =IF(AND($A10<>"",ISFORMULA($A10)),1,"") and so on may return a string value that is used in the arithmetic matrix operations and similar to =""*1 or =Sheet1.D20*1 produces an error result, whereas this error was not propagated before. If you change the formula to =IF(AND($A10<>"",ISFORMULA($A10)),1,0) for all then the calculation works as expected.

To suppress the display of a zero value in this case you can use the number format
[$£-809]#,##0.00;[RED]-[$£-809]#,##0.00;;
Note the trailing ;; semicolons.

Comment 7 David Woodhouse 2015-06-01 22:14:21 UTC
Those are valid and useful suggestions; thanks.

But my existing version used to work in Fedora 21, and an update broke it.

Is that considered acceptable? I could understand being told when I update from Fedora 21 to 22 that "your sheet was broken; you need to fix it".

But getting that in the middle of a *stable* release is a little more surprising.


Note You need to log in before you can comment on or make changes to this bug.