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
Sum works correctly (as it does in Excel)
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)
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?
(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 :-)
> Formally it's certainly incorrect
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 184.108.40.206)]" 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.
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
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.
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.
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.
** Please read this message in its entirety before responding **
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present on a currently supported version of LibreOffice (5.0.5 or 5.1.2 https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System
Please DO NOT
- Update the version field
- Reply via email (please reply directly on the bug tracker)
- Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword
Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa
Thank you for your help!
-- The LibreOffice QA Team This NEW Message was generated on: 2016-04-16
This bug is still present in LibreOffice 220.127.116.11 (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.
(In reply to Xzorg6 from comment #7)
> This bug is still present in LibreOffice 18.104.22.168 (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.