Created attachment 101929 [details] test spreadsheet #1 LibreOffice Calc calculates a wrong sum in the attached spreadsheet: "SUM(A1:A13)" should equal 0, not "1.4210854715202E-014". Tested versions: 4.2.4 (Debian amd64), 4.2.5 (Debian amd64; Windows x86).
Reproduced if using attached file. But I can't reproduce if do it from scratch with new file. Tested with LO 4.2.5.2 - Ubuntu 12.04 x86 @Bob Bib, please try resetting user profile: https://wiki.documentfoundation.org/UserProfile Then try again to repro from scratch in new file. If that resolved, please change status to RESOLVED WORKSFORME
Created attachment 101932 [details] test speadsheet #1.1 1) Unfortunately, deleting the profile dir doesn't help. 2) I've attached a smaller sample spreadsheet, where the sum results in "2.1316282072803E-014" instead of 0.
I've found why I can't reproduce before. Usually I enable "Limit decimals for general number format" and set to '2'. Now I can reproduce from scratch if that setting disabled. Reproduced with: 4.3.0.1, 4.2.5.2 Not reproduced with: 4.1.6.2, 4.0.6.2, 3.6.7.2 But this bug not occured if we use non decimal numbers. Workarounds: 1. Change cell number format to anything, except "Standard"; OR 2. Enable "Limit decimals for general number format"
With master commit 924a28a, pulled 2014-06-28, built and running on debian-wheezy 64-bit on a AMD-64 processor, I see: (*) I see sum 2.13162820280E-014 rather than 1.4210854715202E-014 that the reporter complains of. (*) Sorting the summed range by either ascending value or ascending abs of the value results in the zero which the reporter asks for. So, the problem is just the natural result of using binary floating point numbers while showing decimal numbers to the user. To complicate the situation, computerized addition is not commutative. IMO, using binary floating point numbers by default in a commercial environment is a wrongheaded design choice: just look at the confusion it causes Bob Bib. Still, it is a widespread choice, and I find it hard to imagine that LibreOffice wants to change it. I am setting bug status back to UNCONFIRMED to encourage further discussion. Terry.
I confirmed this occurs with LO, Calligra Sheets, a Python script, and a Ruby script. This issue is caused by the imprecise representation of decimal numbers by digital computers. It is a well known issue in numerical analysis and scientific computing. In all, the addition result is order sensitive with some orderings returning 0.0 and others the value reported in comment 2. Descending and ascending order summed to 0.0 in LO. But this may be an artifact of this specific dataset.
After some consideration and discussion (<http://nabble.documentfoundation.org/Libreoffice-qa-fdo-80638-quot-Incorrect-SUM-with-decimal-numbers-quot-td4114040.html>) I am closing this NOTABUG.
*** Bug 81971 has been marked as a duplicate of this bug. ***