Description: Since last upgrade, all cells that normally give a result "0,00" are displayed for example as "-8,17E-14". To correct the problem, I have to change the format of the numbers from "Standard" to "-1.234,57" Actual Results: For example, for a cell "=SOMME(E15;K16;C15)" where the sum is normally "0,00" Expected Results: "-8,17E-14" is displayed Reproducible: Always User Profile Reset: No Additional Info: display "0,00"
Hi Marcel Thank you for the report. I couldn't reproduce with: Version: 7.2.4.1 / LibreOffice Community Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9 CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded Can you please: - share the information included in Help > About LibreOffice... (it might be "Aide > À propos de LibreOffice..." en français). There is a button to copy everything. - tell us which version you updated from (the version you were using before installing 7.2.4.1) - provide us with a simple example file that shows the issue, or more precise steps to reproduce (including what values are in the cells you referenced). Thank you!
Created attachment 177095 [details] Screenshot open test.xls file
Created attachment 177096 [details] file test.xls
Info requested. 1) Version: 7.2.4.1 (x64) / LibreOffice Community Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9 CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: fr-BE (fr_BE); UI: fr-FR Calc: threaded 2) 7.2.3.2 release 3) See the attached file "test.xls" where all numbers are formatted as "Standard". For example, if I open the file on my PC Window 10, I see for cell E4 "-2,7E-13" iso 0. 4) See the attached file "screenshot 2021-12-22 150229.jpg", just what I see on my PC. Regards.
[Automated Action] NeedInfo-To-Unconfirmed
Confirmed with: Version: 7.2.4.1 / LibreOffice Community Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9 CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded Version: 7.3.0.0.beta1 / LibreOffice Community Build ID: 436f14c25ec1847646b953cf13d0db4f7ca3be57 CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded Version: 7.4.0.0.alpha0+ / LibreOffice Community Build ID: 17a4f4d5e4d49189b43e748271d2d4fa330eef9b CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded But I _don't_ reproduce with: Version: 7.0.6.2 Build ID: 00(Build:2) CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Ubuntu package version: 1:7.0.6-0ubuntu0.18.04.1_lo1 Calc: threaded Version: 7.1.8.1 / LibreOffice Community Build ID: e1f30c802c3269a1d052614453f260e49458c82c CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded I am surprised this issue arises with the 7.2.4.1 release, as it was a security fix release with one single issue corrected...
This is not XLS-specific: I could confirm with the same versions listed above with the file saved as ODS. I can confirm that the workaround is to change the format from "general" to something else.
This is what you get when repeatedly adding binary floating point values. See also https://erack.de/bookmarks/D.html#010203 Note also what happens to the displayed values when column E is widened. Fwiw, Gnumeric quite agrees (with marginal different values) in that results are not 0 as it does not try to eliminate accuracy problems. Note that only changing the display format to a rounding one just hides the proliferation of accuracy errors. In fact you have to round all intermediate values in column E starting from the bottom to make this work, like in E72 =ROUND(SUM(C72;E73;A72);2) and copy&paste that up. Or instead of SUM() use the + operator that is implemented to try to tie values to 0 for marginal minimal accuracy differences, like =C72+E73+A72 and copy&paste that up. I also see no change in 7.2.4, I get the same display values in 7.2.0; however, I do see the difference compared with 7.1.8 and earlier. I assume this is due to the Kahan summation algorithm being implemented for SUM() and some other functions taking cell ranges, see https://wiki.documentfoundation.org/ReleaseNotes/7.2#Calc and bug 137679, where previously SUM() for the first and one subsequent value did something similar as the + operator. Maybe we can add that back as a special case for single values like they are used here.
And it's not only about single values.. The famous sequence 0.1 0.2 -0.3 and permutations as cell range equally produces the raw 2.77555756156289E-17 result that the earlier one-value approxAdd handled.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/730b8aba72356bb8ba0066a5517b1224a4f1e232 tdf#146367 reintroduce approxAdd() similar handling with last summand It will be available in 7.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Note that with this change the results in the attached sample document are still not fully what would be expected, i.e. cells E32, E21, E20, E19 and E18 display unexpected fractional values, but that is due to the *better* accuracy of Kahan summation as used in SUM(), which for repeated summation of inexact (not exactly representable as binary floating point) values delivers these. The better approach for this document would be to use the + operator instead of repeatedly SUM() with single values, for example in cell E32 use =C32+E33+A32 instead of =SUM(C32;E33;A32) to benefit from the + operator eliminating opposite sign small equalish fractions. (and use that throughout column E).
Pending review https://gerrit.libreoffice.org/c/core/+/128432 for 7-3 https://gerrit.libreoffice.org/c/core/+/128433 for 7-3-0 https://gerrit.libreoffice.org/c/core/+/128464 for 7-2
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/26b3fc1f31b682fcc7ac0c10af07e64cffbe86e7 A unit test for tdf#146367 It will be available in 7.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-3": https://git.libreoffice.org/core/commit/a4fb21c5275028378f639150ed1bcc26e1c3836f tdf#146367 reintroduce approxAdd() similar handling with last summand It will be available in 7.3.1. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-2": https://git.libreoffice.org/core/commit/7fd980a607eead2c6cf6557c07a9c25cb5b1a5d4 tdf#146367 reintroduce approxAdd() similar handling with last summand It will be available in 7.2.6. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-3-0": https://git.libreoffice.org/core/commit/7f0bbbea121335dc7d9a97be88ec7803c3a90bb1 tdf#146367 reintroduce approxAdd() similar handling with last summand It will be available in 7.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.