Bug Hunting Session
Bug 80638 - Incorrect SUM with decimal numbers
Summary: Incorrect SUM with decimal numbers
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.4.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL: http://bugs.debian.org/750705
Whiteboard:
Keywords: regression
Depends on:
Blocks:
 
Reported: 2014-06-28 16:27 UTC by Bob Bib
Modified: 2015-03-04 17:04 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
test spreadsheet #1 (21.34 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-28 16:27 UTC, Bob Bib
Details
test speadsheet #1.1 (15.62 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-28 19:00 UTC, Bob Bib
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Bob Bib 2014-06-28 16:27:04 UTC
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).
Comment 1 ign_christian 2014-06-28 17:43:50 UTC
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
Comment 2 Bob Bib 2014-06-28 19:00:01 UTC
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.
Comment 3 ign_christian 2014-06-29 08:38:15 UTC
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"
Comment 4 Terrence Enger 2014-06-29 16:39:21 UTC
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.
Comment 5 Planas 2014-06-29 18:12:41 UTC
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.
Comment 6 Terrence Enger 2014-06-29 20:20:08 UTC
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.
Comment 7 sophie 2014-07-31 16:53:14 UTC
*** Bug 81971 has been marked as a duplicate of this bug. ***