Bug 41480 - Sum function doesn't work accurately over 900 lines
Summary: Sum function doesn't work accurately over 900 lines
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-10-05 08:19 UTC by me
Modified: 2016-07-15 05:48 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Examples of sums going beyond 900 (30.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-10-05 08:19 UTC, me
Details

Note You need to log in before you can comment on or make changes to this bug.
Description me 2011-10-05 08:19:28 UTC
Created attachment 52010 [details]
Examples of sums going beyond 900

Steps to reproduce:

Create a file with over 1,500 items in (to 2 or 3dp).

Create a sum function for a1:a1000
The result will have a total ending .xxx9999999

Create a sum function for a1:a1500

The result will have a total ending .xxx9999998



Expected results:

Sum works correctly (as it does in Excel)



Examples: 

See attached spreadsheet example.  The point where is starts summing wrong is not always consistent, row ~938.

Also if you sum a1:900 and a901:a1000 separately you can sum those two sums and not get the extra decimal place inaccurate (see cell B8 vs C2)
Comment 1 Rainer Bielefeld Retired 2011-10-05 22:15:11 UTC
@me@phillg.com
No idea why you expect what result, "Because EXCEL ..." is not a comprehensible  reasoning. Please contribute a comprehensible reason. Why do you expect that EXCEL result is correct?
Comment 2 vitriol 2011-10-05 22:27:23 UTC
(In reply to comment #1)

> No idea why you expect what result, "Because EXCEL ..." is not a comprehensible
>  reasoning. Please contribute a comprehensible reason. Why do you expect that
> EXCEL result is correct?

Calc result looks like a classic floating point precision error. Formally it's certainly incorrect, because the sum can't have more significant decimal than addends :-)
Comment 3 Rainer Bielefeld Retired 2011-10-05 23:33:38 UTC
> Formally it's certainly incorrect

@vitriol:
Yes, of course. That's done much better by Gnumeric 1.10.14, When I open reporter's sample with Gnumeric, results in the second row all have 3 decimals (what looks formally correct).

When I modify A13 from "59,916" to "59,9161", the results get a tailing "1"

That's also better in OOo 1.1.4, B2 shows "33939,094000", what's formally correct (I selected "6 decimals")
Also OOo 3.1.1    shows "33939,094" in B2
Also OOo-dev 3.2  shows "33939,094" in B2 and when I modify A13 from "59,916" 
                                    to "59,9161", the results get a tailing "1"
OOo 3.4 shows the same problem as LibO, C38 shows lots of decimals (35280,0439999999)

Problem is [Reproducible] with "LibreOffice Portable 3.3.3  - WIN7  Home Premium (64bit) German UI [OOO330m19 (Build:301  Tag 3.3.3.1)]" and 
with "LibreOffice 3.4.3  - WIN7 Home Premium (64bit) German UI [OOO340m1 (Build:302)]" and with
with Server installation of Master "LibO-dev 3.5.0 – WIN7 Home Premium (64bit) English UI [(Build ID: 81607ad-3dca5fd-da627d2)]"
EXCEL Viewer will show correct result for document saved as .xls.

My conclusion:
I doubt that it's a "classic floating point precision error", other Spreadsheet programs do not show the problem. Seems that problem has been always in LibO

@Kohei:
Please feel free to reassign (or reset Assignee to default) if it’s not your area or if provided information is not sufficient. Please set Status to ASSIGNED if you accept this Bug.
Comment 4 Björn Michaelsen 2011-12-23 13:23:11 UTC
Since all new unconfirmed bugs start in state UNCONFIRMED now and old unconfirmed bugs were moved to NEEDINFO with a explanatory comment, all bugs promoted above those bug states to NEW and later are automatically confirmed making the CONFIRMED whiteboard status redundant. Thus it will be removed.
Comment 5 Winfried Donkers 2015-03-30 11:32:33 UTC
The problem is caused by the fact that decimal values cannot be translated _exactly_ to binary values. Even though the diffences are extremely small, with SUM they sometimes show and are annoying.

Alas, I have not (yet) found a solution yet.
Comment 6 tommy27 2016-04-16 07:22:42 UTC Comment hidden (obsolete)
Comment 7 n.b 2016-07-14 21:24:30 UTC
This bug is still present in LibreOffice 5.1.4.2 (July 2016) on any OS.
With the same behavior reported in 2011.

With the attachment example, we can see :

B2=sum(A1:A900) : 33939,094
B5=sum(A901:A1000) : 3303,514
B8=sum(B2+B5) : 37242,608 (correct)

But if we sum directly A1 to A1000 :

C2=sum(A1:A1000) : 37242,6079999999 (not correct)

Values from A1 to A1000 are not over 3 decimals, then the sum shouldn't have more than 3 decimals.
Comment 8 Winfried Donkers 2016-07-15 05:48:29 UTC
(In reply to Xzorg6 from comment #7)
> This bug is still present in LibreOffice 5.1.4.2 (July 2016) on any OS.
> With the same behavior reported in 2011.
> 

As explained in comment#5 the cause is inherent to the way computers work.

'Fixes' will move the symptoms (the symptoms will be no longer present in your attachment, but may occur in other instances where they currently don't occur) or make the application slow (without fixing all cases).

The simplest way to avoid the symptoms is to use ROUND() when calculating sums of many non-integer values, or to use cell format, to limit the number of decimal places.