Description: A simple table of bank transactions extracted from CSV data (i.e. no formulae, or other hidden data - simply alphanumeric fields) was being used, and values checked by a simple sum ([J90]=J89+E90) comparing against the precomputed values in the table. At row 158 the sum becomes an extended decimal value, whereas prior to this row the numbers are correctly confined to 2 decimal places. However, the sum displayed at the bottom of the spreadsheet window shows erronious valuse with recurring decimal digits earlier in the table at row 128. The data has been checked thoroughly and all values within the CSV are only 2 decimal places. The addition error occurs in the inner workings of LibreOffice Calc somewhere. The table and the imported data are 100% correct. The cell calculation formula appears to be simple, but there is evidently something else going on, other than simple addition. The error shows in the table as -9.0955021292416E-14 at line 158, however the sum appears to acquire the error earlier in the sequence at line 128, where the value is XX.XX99999999999. A small experiment setting the displayed digits in cell format causes the error to disappear (irrispective of the number of decimal places selected) - reverting the cell format to general causes the error to reappear. Steps to Reproduce: 1. Open CSV 2. create sum in column at edge of data (column J) 3. find row with value with more than 2 decimal places. Actual Results: Rows after 128 can be seen to have recurring or additional decimal digits. Expected Results: all values computed should match the 2 decimal place values otherwise present in the original data. Reproducible: Always User Profile Reset: No Additional Info: Data on the table cannot be shared due to data protection concerns. However, the data is not special in any way - comprises of just text and numbers.
An update to LibreOffice is being installed. Another test will be performed to test if the issue has already been addressed.
Expected. See https://help.libreoffice.org/7.6/en-GB/text/scalc/01/calculation_accuracy.html?DbPAR=CALC and https://wiki.documentfoundation.org/Faq/Calc/Accuracy Use proper rounding in calculation steps, in this case to 2 decimals.
There is no point using any form of rounding if the values do not ever have any additional digits! You cannot mark as resolved just because there is a work around. The issue should not be occuring in the first instance. 1.11 + 2.22 should always equal 3.33 without any need for rounding. The Calc application is performing some function on the values resulting in 1.11 + 2.22 being evaluated to 3.329999999999. This is clearly an internal error! Do your users a favour and dont jump to conclusions.
Having read the linked document, I understand that this is a representation issue, however, complicating a users calculations to simplify an internal representation issue is frankly not good. Almost every other calculation system on the planet performs the action of simple addition without slowing down significantly or without needing user intervention. If the system must use a single representation that is incapable of accuracy, it should be able to flag those representations, and take mitigating actions. Fixed value double integer calculation and other solutions are available to aleviate the floating point issue. Forcing a rounding calculation potentially introduces errors on the user side.