Description: If you enter a value with more than 15 digits, e.g. 0.9999999999999994, it is shown in formulas abbreviated to 15 digits, 0.999999999999999, but the trailing 4 is accounted in calculations, e.g. multiplying that value with 6 leads to '6'. ( Small differences like that are mostly covered by shortened display and 'approximate equality' and the like, to get detailed info use 'rawsubtract'. ) In a save - close - load cycle the trailing 4 is lost, and the calculation 6 times the new value results in 5.99999999999999. That leads to different behavior / results between sheet construction and later use, which should - IMHO - be unwanted. Steps to Reproduce: see description Actual Results: different behavior between input and loaded file. Expected Results: coherent behavior, either truncate / falsify / round values on input, or keep precision in save - load. Reproducible: Always User Profile Reset: No Additional Info: Version: 24.2.5.2 (X86_64) / LibreOffice Community Build ID: 420(Build:2) CPU threads: 8; OS: Linux 6.8; UI render: default; VCL: x11 Locale: en-US (en_US.UTF-8); UI: en-US Debian package version: 4:24.2.5-4 Calc: threaded happened with older versions too.
Hello, Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided. (Please note that the attachment will be public; remove any sensitive information before attaching it. See <https://wiki.documentfoundation.org/QA/FAQ#sanitize> for help on how to do so.) Also, I have tried to reproduce the bug with the steps provided but was not able to reproduce it. What format were you saving the file? I saved the file in .ods format. Please attach the document where you are seeing it not keep the trailing 4 and multiplying by 6 = 5.99999999999999 It may be helpful if you provide more detailed steps as well. Tested with: LibreOffice version 24.8.2.1 (current version) and in 25.2.0.0 (master build) Master Build Version: 25.2.0.0.alpha0 TinderBox: MacOSX-aarch64@tb92-TDF, Branch:master, Time: 2024-11-06 05:23:09
Created attachment 197720 [details] file with result 5.99999999999999
hello @ Nicole, thanks for checking, 1. enter 0.9999999999999994 in cell A4, 2. see display 0.999999999999999 without trailing '4', 3. enter '=6 * A4' in A5, 4. see result '6' in A5, 5. save file as *.ods, 6. close sheet, 7. load file, 8. see result in A5 changed to 5.99999999999999, 9. assume value in A4 changed to 0.999999999999999, without the '4', 10. problem: differences between construction-time and run-time, 11. hard to understand, not user friendly, 12. wish: consistent behavior, either 12a. keep precision in file save and load, or 12b. truncate on input rather than file save, think that's what Excel doe's.
Created attachment 197728 [details] Screenshot of Precision as shown Hello, Thank you for providing the sample document and detailed instructions of the issue you are experiencing. I went ahead and followed your instructions and I still was not seeing the result of 5.99999999999999 but was still showing as 6 even when saving and closing and re-opening the .ods document. However, in the LibreOffice Calc settings, I did not have the "Precision as shown" setting enabled. Once I updated the setting to be enabled for "Precision as shown" and then followed your instructions again, it did show the 5.99999999999999 and not the expected result 6. I recommend you check if you have the "Precision as shown" enabled and if so, disable it and try your test again. I have attached a screenshot of the "Precision as shown" setting.
hello @ Nicole, thanks for checking, 'Precision as shown' is off here, issue is quite easy, entering 0.999999999999999 in a cell without the '4' and that cell times 6 in another cell produces 5.99999999999999. Calc stores the original input of 0.9999999999999994 in the file without the 4 ( inspect the content.xml inside the ods file, and stores '6' as text for the result cell. If new calculating with the stored 0.999999999999999 it will produce 5.99999999999999, _assume_ you have some setting like 'recalc on load' off? Pls. check and / or pls. try recalc with F9, ctrl-shift-F9 or similar. If you still cant repro it could be suspected your system stores more digits, pls. try to check with an unzipper and xml viewer. :-)
reporter's strange, unexpected observations (with tailing "4") for me are REPRODUCIBLE with Installation of Version:25.2.4.3 (X86_64) Build ID: d5143c058bfdc0f5674c3e0a88fae2f9cbe28a0a CPU threads: 12; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: de-DE (de_DE); UI: en-US; Theme: Automatic Colibre Calc: threaded, My normal User Profile Still REPRODUCIBLE with Server Installation of Version: 25.8.0.0.alpha0+ (X86_64) Build ID: 8049d3f2345d5e6ead9fcb7b84f376fc84b82706 CPU threads: 12; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: de-DE (de_DE); UI: en-US; Theme: Automatic Colibre Calc: threaded Special User Test Profile for testing, Copy from my 25.2.3.2 Normal Use Profile But currently I haven't understood all that good enough to decide whether we have a bug here or not.
Created attachment 201508 [details] screencast shows that input of tailing "4" in input line influences calculation result, but the "4"is invisible in input line and cell.
Reason of the problem might be the same as for "Bug 150635 - Numbers larger than 15 digits cannot be FORMATED as TEXT" as explained in "Frequently asked questions - Calc: Accuracy problem" <https://wiki.documentfoundation.org/Faq/Calc/Accuracy> ❓
(In reply to Rainer Bielefeld Retired from comment #8) The FAQ answer might explain why it's impossible to calculate with more than 15 decimals? I'm still rather worried.
In the end of the day all bin-FP accuracy / rounding issues can be summarized under "bin-FP issues", but it is better to distinguish the differences between them such as cancellation, approximated representation, fit-in rounding ... and to treat them appropriately. Similarly, you can group all "libreOffice calc tries prettyfying and can't get it to work consistently" issues under "unsuccessful prettyfying", which is Mike Kaganski's level of "There is nothing exact in floating-point calculations in Calc, ..." ( https://bugs.documentfoundation.org/show_bug.cgi?id=154792 ), but that does little to change / improve anything. This bug is very special or exactly the incontinence to evaluate values when entering into a table as 16-digit exact, but when writing into a file to save only 15-digit exact. It only takes a few such inconsistency points and persistent adherence to them to very effectively block coherent calculations. ( Partly ) castrating an already weak system (IEEE 754) in order to improve it is a questionable strategy and leads to even weaker results.