A1 951.6 A2 2045.15 A3 =SUM(A1:A2) A4 =A3*0.1 A5 =A3-A4-0.01 With no formatting A5 shows as 2697.065. If you format the cell to only show 2 digits, it goes to 2697.06 instead of 2697.07. Other similar values I have tried in A1 and A2 all end up with the expected answer in A5 with formatting applied. We have a contractor who submits bills in xlsx format, and 4 times in 2023 this bug caused us to pay a penny different than they expected (it was caught by auditors at the first of this year).
Repro in recent alpha.
As with other functions (that are not fixed yet ATM), repro in 7.4. Not repro in 7.3. IDK when Calc will be usable again. Four major versions with incorrect results is more than enough.
Note that =ROUND(A5;2) gives 2697.07 in 24.2.2.1, giving a discrepancy.
FWIW and JIC someone is tempted to suggest using ROUND()... Using ROUND() only on the last formula, might or might not get the expected result, because of similar issues in prior calculations (i.e. accumulation of "errors"). Maybe there is some mix-up with "precision as shown"(???). Users would expect to see 5+ rounded up, with whichever method. This would be a reasonable expectation.
(In reply to Michael from comment #0) > We have a contractor who submits bills in xlsx format, and 4 times in 2023 > this bug caused us to pay a penny different than they expected (it was > caught by auditors at the first of this year). (In reply to ady from comment #2) > IDK when Calc will be usable again. Four major versions with incorrect > results is more than enough. (In reply to ady from comment #4) > FWIW and JIC someone is tempted to suggest using ROUND()... 1. The change is caused by https://gerrit.libreoffice.org/c/core/+/129948; and it made the display more *correct* WRT the actual value of 2697.0649999999996, which is rightfully rounded to 2697.07. The reason why the calculation produces that value is explained in FAQ [1]. 2. In the specific case that Michael describes, where the rounding is not intended just for display, but is the actual result of the calculation, the *correct* thing to do *is* use of ROUND function. 3. But generally, the difference between the ROUND and display rounding is not nice. I need to think how to fix this problem. 4. ady: please avoid emotional, unrelated phrases that don't add anything useful to the issue, but make indirectly make others feel bad. I don't feel comfortable working on this issue now, after your "contribution". [1] https://wiki.documentfoundation.org/Faq/Calc/Accuracy
https://gerrit.libreoffice.org/c/core/+/165142
(In reply to Mike Kaganski from comment #5) > 2697.0649999999996, which is rightfully rounded to 2697.07. Indeed, that is a typo; the intended phrase was "which is rightfully rounded to 2697.06".
Mike Kaganski committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/805dd6bee49164d9a77de4ea9e0d53b416daca7a tdf#160306: make sure that SvNumberFormatter agrees with ROUND output It will be available in 24.8.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.
Mike Kaganski committed a patch related to this issue. It has been pushed to "libreoffice-24-2": https://git.libreoffice.org/core/commit/581b9c2752d6cedd5622c57cd38b1f8a36d53169 tdf#160306: make sure that SvNumberFormatter agrees with ROUND output It will be available in 24.2.3. 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.
Mike Kaganski committed a patch related to this issue. It has been pushed to "libreoffice-7-6": https://git.libreoffice.org/core/commit/b250f480831b4726801868ae203517938cd1c704 tdf#160306: make sure that SvNumberFormatter agrees with ROUND output It will be available in 7.6.7. 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.
*** Bug 160439 has been marked as a duplicate of this bug. ***