Bug 1219287

Summary:	Recent update broke array SUM()
Product:	[Fedora] Fedora	Reporter:	David Woodhouse <dwmw2>
Component:	libreoffice	Assignee:	Eike Rathke <erack>
Status:	CLOSED NOTABUG	QA Contact:	Fedora Extras Quality Assurance <extras-qa>
Severity:	unspecified	Docs Contact:
Priority:	unspecified
Version:	21	CC:	caolanm, dtardon, erack, ltinkl, mstahl, sbergman
Target Milestone:	---
Target Release:	---
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2015-06-01 17:26:36 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

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.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.