Bug 134669

Summary: Calculation should show ZERO, but shows E-12 number
Product: LibreOffice Reporter: Bela Lantos <belantos>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: CLOSED NOTABUG    
Severity: normal CC: erack, himajin100000
Priority: medium    
Version: 7.0.0.1 rc   
Hardware: x86-64 (AMD64)   
OS: Windows (All)   
Whiteboard:
Crash report or crash signature: Regression By:
Attachments: repayment spreadshield

Description Bela Lantos 2020-07-09 02:34:53 UTC 
Description:
I have a spreadsheet that calculates monthly repayments and shows the amount owed fields. When the amount owed should be ZERO the calculation shows -1.82609E-12. The problem is present in 6.4 - 7.0

I have just reinstalled the laptop.

SoftMaker Office displays the values correctly

Actual Results:
-1.82609E-12


Reproducible: Always


User Profile Reset: No



Additional Info:
I can send you the file if you give me an email address
Comment 1 Bela Lantos 2020-07-09 02:55:14 UTC 
Created attachment 162822 [details]
repayment spreadshield

Here is the file, I removed some personal details
Comment 2 Eike Rathke 2020-07-09 10:28:56 UTC 
Repeated addition of binary floating point values leads to inaccuracy if values aren't exactly representable, that's a fact of the IEEE 754 double format. If in your document you widen columns D, F and H you'll see that already the values in D22, F39 and H43 start to be slightly off. There are two measures to calculate such series of repeated addition correctly, either round each calculation to the desired decimals, here ROUND(...;2), or format all values to two decimals with a number format 0.00 (which in this case already *displays* the end result as 0.00) and maybe you'll also have to switch on General Calculations "Precision as shown" under Tools -> Options -> Calc -> Calculate if the repeated series gets longer with accumulated inaccuracy.

See also https://erack.de/bookmarks/D.html#010203