Bug 153490 - Floating point error
Summary: Floating point error
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.0.7.3 release
Hardware: All Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-02-09 02:50 UTC by Gustavo de Leonardis
Modified: 2023-02-09 06:45 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Gustavo de Leonardis 2023-02-09 02:50:47 UTC 
Description:
LibreOffice Calc is making a floating point error.
Takes a list of values "xx,xx", insert average function, calculates the difference between each value and average, then try to sum the differences (which must be "0"): "9,76996261670138E-15"


Steps to Reproduce:
1.Write this values in a single column:2,72
-2,28
-3,28
4,72
-0,28
2,72
-5,28
-1,28
3,72
6,72
5,72
1,72
-3,28
-0,28
1,72
4,72
-3,28
-3,28
-8,28
-0,28
0,72
-4,28
-0,28
-1,28
1,72

2. Insert SUM function
3.

Actual Results:
9,76996261670138E-15



Reproducible: Always


User Profile Reset: No

Additional Info:
Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.12
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: x11; 
Locale: es-AR (es_AR.UTF-8); Calc: group
Comment 1 ady 2023-02-09 05:37:07 UTC 
I tested this in:

Version: 7.4.5.1 (x64) / LibreOffice Community
Build ID: 9c0871452b3918c1019dde9bfac75448afc4b57f
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: en-US (es_AR); UI: en-US
Calc: CL

(Newer version and different OS)

While the result of SUM() and AVERAGE() functions for this set of values is 0 (zero) in the version I tested this, it still shows non-zero values in the bottom-right area in the status bar.

So:

1. @Gustavo, it might be worth trying a newer version of LO, if it is possible.

2. As of LO 7.4.5, there could be some potential inconsistency, at least from the POV of common users, between the results of functions used within cells and those used in the quick calculations area presented in the status bar. I'm not saying it needs to be changed, but it has to be said/acknowledged (and perhaps documented, if it is not already? IDK).

3. FWIW see https://wiki.documentfoundation.org/Faq/Calc/Accuracy
Comment 2 Mike Kaganski 2023-02-09 06:45:50 UTC 
As mentioned in comment 1, this is not a bug.
Also, later versions have introduced a better summation algorithm (bug 137679), so the raw sum is 6.66E-16 (shown in the status bar, as ady mentions), and it collapses to 0 in SUM.