Download it now!
Bug 109189 - Statusbar sum and average calculation with decimal values equivalent to zero sometimes shows incorrect negative exponential value
Summary: Statusbar sum and average calculation with decimal values equivalent to zero ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Statusbar
  Show dependency treegraph
Reported: 2017-07-18 11:39 UTC by C.Drewke
Modified: 2019-08-16 13:17 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

sample (10.31 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-07-24 20:00 UTC, Yousuf Philips (jay) (retired)

Note You need to log in before you can comment on or make changes to this bug.
Description C.Drewke 2017-07-18 11:39:32 UTC
When you select multiple numbers of which at least 6 in a column are negative and at least 2 of those have decimal values the calculated sum has a rounding error.

This bug is also present in a BASE database with values that are calculated with the SUM function.

Steps to Reproduce:
1. Enter the following values into a fresh CALC table (below each other):

2. mark all values

3. observe the rounding error for the sum in the bottom right corner

Actual Results:  
Sum: -7,105427357601E-15

Expected Results:
Sum: 0

Reproducible: Always

User Profile Reset: No

Additional Info:
[Information automatically included from LibreOffice]
Locale: de
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Linux (All)
OS is 64bit: yes
Builds ID: LibreOffice

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:54.0) Gecko/20100101 Firefox/54.0
Comment 1 Xavier Van Wijmeersch 2017-07-19 09:22:51 UTC
I can't reproduce

Build ID: SlackBuild for 5.3.4 by Eric Hameleers
CPU Threads: 8; OS Version: Linux 4.9; UI Render: default; VCL: kde4; Layout Engine: new; 
Locale: nl-BE (en_US.UTF-8); Calc: group


Build ID: 8c82e4877181f55f2fe186b341d504e5782a4f9c
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 2 Jacques Guilleron 2017-07-24 13:50:37 UTC
Hi C.Drewke,

This sum function in Status bar follows the cells format. By default, there is no value for decimal places with ths Standard format, you get so the greatest precision and no rounding.
If I enter =SUM(A1:A6) into A7, I get here 0, a rounded value, with the Standard format.

To make some differences, Excel give also a rounded value 0, when A1:A6 is selected and -7,10543E-15 precision only when Sum in A7 is selected too, still with the Standard format.
With any decimal place number, Excel give always a simple 0 for A1:A6 selection and follows the cell format when A7 is selected and formatted.

Do you expect the same behavior?
Comment 3 C.Drewke 2017-07-24 14:07:24 UTC
I am not sure I fully understand what you mean - 

When I use "=SUM(A1:A6)" in A7 it also calculates "0" for me.

When I format the cells to use only 2 decimals it shows 0,00

When I remove the negative ("-") from the values I get a precise 288,2 when I select cells A1:A6 without formatting them previously

When I import these values from a database (mysql via a LibreOffice Base ODB in my case) with a SUM calculation in the SQL query it still outputs "-7,105427357601E-15". The fields that are used have the double(15,2) format.

As "-7,105427357601E-15" is also displayed when running that query from the ODB file I think this bug is not in based in Calc ...
Comment 4 Yousuf Philips (jay) (retired) 2017-07-24 20:00:05 UTC
Created attachment 134826 [details]
Comment 5 Yousuf Philips (jay) (retired) 2017-07-24 20:10:40 UTC
1. Open attachment 134826 [details]
2. Select the values in either columns B, D, or H
3. Look in the statusbar section that normally shows 'Average: ; Sum: 0' and notice that it has a negative value rather than a 0.

Build ID: 888f3f18cf42ca08f55a5e4826b4298f391e14aa
CPU threads: 2; OS: Linux 4.4; UI render: default; VCL: gtk2; 
Locale: en-US (en_US.UTF-8); Calc: group
Comment 6 QA Administrators 2018-07-25 02:39:07 UTC Comment hidden (obsolete)
Comment 7 Oliver Brinzing 2019-08-16 13:17:24 UTC
reproducible with:

Version: (x64)
Build ID: 057fc023c990d676a43019934386b85b21a9ee99
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US