Ubuntu: Does LibreOffice calc have a rounding bug in sum()?



Question:

LibreOffice Version: 4.2.8.2 Build ID: 420m0(Build:2)

I have a simple spreadsheet for double entry home accounting which works fine except on one row where, in conditional formatting, I am using SUM() with the values £-23.99, £8.00, and £15.99. If the result is not 0, I get a red background to warn me that I haven't balanced the line. The line mentioned has a red background.

Looking at each of the values without currency format, they are as expected to 20 decimal places (e.g. -23.99000000000000000000). However, the result of SUM(x,y,z) returns 3.6E-015 for that row only. All other rows are fine. If I repeat the same data set on a different row, I get the same error. What is 'special' about these numbers?

Obviously I can patch it by rounding my result, but I wonder whether its a known bug?


Solution:1

What you're seeing is binary conversion error. Those numbers are, at some intermediate point, stored in binary form in processor and/or RAM, and converted back to decimal for comparison (or converted to binary for comparison) That introduces a difference in the last few decimals that can be represented by (most likely) a 32-bit signed float value. It's not an error in Libre Office Calc, as such; rather its an inherent limitation in doing decimal arithmetic on a binary machine.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »