Bug 94394 - Calculation Error: Possibly accumulation of small floating-point errors
Summary: Calculation Error: Possibly accumulation of small floating-point errors
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
: 100552 (view as bug list)
Depends on:
Reported: 2015-09-21 04:35 UTC by Martin L. Buchanan
Modified: 2016-06-23 22:48 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Personal finance tracking spreadsheet with personal info removed (22.81 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-09-21 04:35 UTC, Martin L. Buchanan

Note You need to log in before you can comment on or make changes to this bug.
Description Martin L. Buchanan 2015-09-21 04:35:58 UTC
Created attachment 118888 [details]
Personal finance tracking spreadsheet with personal info removed

Using a Calc ods spreadsheet to track personal finances, with a long series of rows with a formula like =C59+A60-B60 (new balance = previous balance + any money in minus any money out).

See cell C81 in the attachment re. the problem, showing that 1 minus 1 is -2.08721928629529E-014 rather than 0.

If the spreadsheet is using floating-point, or (worse) single-precision floating-point to represent numbers, the math needs to be done right so that floating-point deltas (between visible numbers like 0.03 and the closest floating-point representation) do not accumulate.
Comment 1 Eike Rathke 2015-09-21 14:22:39 UTC
Of course Calc uses IEEE 754 double precision floating point values. However, that does not resolve the fact that not all decimal numbers are exactly representable in binary floating point formats. That is not a fault of Calc and Calc is not the only spreadsheet program suffering from it. See also https://support.microsoft.com/kb/78113 and https://blogs.office.com/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ and https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel and http://floating-point-gui.de/

Other algorithms could be implemented, but only at the cost of performance on large data sets, which would be a different RFE.

If you need exactly rounded results you have to use the ROUND() spreadsheet function wherever appropriate or activate the Tools->Options->Calc->Calculate "Precision as shown" option, which rounds every result to the precision of the number format that is used to display the value.
Comment 2 m_a_riosv 2016-06-23 22:48:54 UTC
*** Bug 100552 has been marked as a duplicate of this bug. ***