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: 2018-07-25 02:39 UTC (History)
2 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
** 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 with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

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) from http://downloadarchive.documentfoundation.org/libreoffice/old/

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: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team