Bug 154792 - Calc Round Down is Rounding Up for some values
Summary: Calc Round Down is Rounding Up for some values
Status: REOPENED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:7.6.0 target:7.5.4
Keywords:
: 154797 154871 (view as bug list)
Depends on:
Blocks: Calc-Cells
  Show dependency tree / graph
 
Reported: 2023-04-13 19:31 UTC by NOYB
Modified: 2024-05-10 14:41 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Attachments
LibreOffice Calc Round Down Errors Screen Capture (141.95 KB, image/jpeg)
2023-09-04 08:54 UTC, NOYB 		Details
MS Excel Round Down No Errors Screen Capture (137.18 KB, image/jpeg)
2023-09-04 08:56 UTC, NOYB 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description NOYB 2023-04-13 19:31:03 UTC 
Description:
Round Down is Rounding Up
LibreOffice Calc 7.5.2.2
Formula: =ROUNDDOWN(726498.789999828,2)
Actual   Result: 726498.79
Expected Result: 726498.78


Steps to Reproduce:
1.Formula: =ROUNDDOWN(726498.789999828,2)
2.
3.

Actual Results:
726498.79

Expected Results:
726498.78


Reproducible: Always


User Profile Reset: No

Additional Info:
Comment 1 Mike Kaganski 2023-04-13 19:46:58 UTC Comment hidden (obsolete)
Comment 2 Mike Kaganski 2023-04-13 22:08:57 UTC 
Hmm, this might be intentional: rtl_math_round uses rtl::math::approxFloor, to explicitly round the passed value to nearest, when the distance is *very close*. Likely this is to avoid users' surprise, when the really small deviation is caused by previous calculations inaccuracies. Likely there's a trade-off.
Comment 3 Mike Kaganski 2023-04-13 22:15:08 UTC 
*** Bug 154797 has been marked as a duplicate of this bug. ***
Comment 4 NOYB 2023-04-13 23:08:28 UTC Comment hidden (no-value)
Comment 5 Mike Kaganski 2023-04-14 08:10:19 UTC 
(In reply to NOYB from comment #4)

Please don't claim things like that, when you don't understand it.
There is *nothing* exact in floating-point calculations in Calc, just as well as in Excel. If that doesn't fit your task, then you need another tool. This issue could be a problem or not (I hope Eike would clarify), but knowing your tool is crucial, and claiming what it must is useless.
Comment 6 NOYB 2023-04-14 10:50:30 UTC Comment hidden (no-value)
Comment 7 Eike Rathke 2023-04-14 12:09:34 UTC 
(In reply to NOYB from comment #6)
> Oh but I do understand it.
I doubt that. You probably don't know why all this not-straight-forward rounding is implemented. Just take a look at the example of bug 124286
=ROUNDDOWN(8.94-8;2)
that for 8.94-8 with the floating point inaccuracy already produces 0.9399999999999995 which following the "rounding a number to nth digit is exact" would produce 0.93 instead of the expected 0.94, hence we try to account for inaccuracy which works for many but not all values and needs to be refined.


> Within the
> precision limits of the underlying system e.g. 19 digits etc.
There is no 19 digits precision. IEEE 754 double has a decimal digits precision of 15 to 17.
Comment 8 NOYB 2023-04-14 18:33:49 UTC Comment hidden (no-value)
Comment 9 Eike Rathke 2023-04-14 19:17:39 UTC 
There is no decimal representation in binary floating point hence any decimal "nth digit" does not exist for values other than multiples of powers of two. Period.

(In reply to NOYB from comment #8)
> If the user want's it rounded down to 2 digits then 0.93
> is the correct answer/result for a value of 0.9399999999999995.
It is not if that 0.9399999999999995 is a result of the calculation of 8.94-8 because the exact number 0.94 is not representable in binary floating point and the nearest value is 0.939999999999999946709294817992486059665679931640625 and the user expects a ROUNDDOWN(8.94-8;2) to be ROUNDDOWN(0.94;2) to equal 0.94 (which it never is).


I don't say we can't have a better implementation, I just state the complexities.
Comment 10 NOYB 2023-04-14 20:23:47 UTC 
You're building a flaw into the ROUNDDOWN function in order to accommodate truncated results of the upstream calculation.  8.94-8 is 0.94.  Rounding that up, down or nearest is 0.94.  Exactly.  Not approximately.

But providing the truncated result of 8.94-8 (0.9399999999999995) to the ROUNDDOWN function should result in 0.93.  Which is the correct result of the ROUNDDOWN function for the given input.  ROUNDDOWN(0.9399999999999995,2) should correctly result in 0.93.  

The flaw is upstream with the truncated result of 8.94-8 (0.9399999999999995) being used rather than providing the correct result of 0.94 to the ROUNDDOWN function.  GIGO

The above are considering 0.9399999999999995 is retained as truncated value rather than the correct value of 0.94.
(long ago been corrected)

Code the ROUNDDOWN function to do what it is supposed to do.  "Rounds a number down to a predefined accuracy."  Stop using it to correct incorrect upstream results.
Comment 11 Mike Kaganski 2023-04-14 21:38:55 UTC 
(In reply to NOYB from comment #10)
> truncated results of the upstream calculation.  8.94-8 is 0.94.  Rounding
> that up, down or nearest is 0.94.  Exactly.  Not approximately.

(In reply to NOYB from comment #6)
> Oh but I do understand it.  I never said floating point was exact.

Hmm... Did that person made self-contradicting statements, or have I misread? Does that person even tried to look up the exact representation and the exact *value* of "0.94" in IEEE 754 double precision?

But well, I remember that erAck's "I fix bugs even for ..."
Comment 12 NOYB 2023-04-14 22:27:53 UTC Comment hidden (no-value)
Comment 13 Mike Kaganski 2023-04-15 00:45:33 UTC 
(In reply to NOYB from comment #12)

Please ask Excel to count `=ROUNDUP(0.01-(10.01-10);2)`.
You just imagine that Excel knows "how to count properly", and provides you "the correct result". It just does mistakes in other cases, because uses other rounding, and there's nothing immune to the fundamental problem, just because, well, Excel (and Calc) has no fingers and toes.
Comment 14 Mike Kaganski 2023-04-15 00:56:15 UTC 
Or ask the same Excel count =ROUNDDOWN(1234,567-1135,678;3) (since we here talk about ROUNDDOWN).

Please stop your ignorant but arrogant narration, and just wait until Eike improves things *as much as possible*.
Comment 15 Commit Notification 2023-04-15 01:41:26 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/8186a01f2a26f05645a2a3c9c93b453bd35b796f

Resolves: tdf#138220 tdf#154792 Avoid double rounding; tdf#124286 follow-up

It will be available in 7.6.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.
Comment 16 Commit Notification 2023-04-15 19:14:54 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/e68cd91651c53c5c228d84582b1062d8fb9a7077

Add ROUND(DOWN|UP) samples to unit test, tdf#154792 tdf#124286 tdf#138220

It will be available in 7.6.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.
Comment 17 Eike Rathke 2023-04-17 21:58:47 UTC 
*** Bug 154871 has been marked as a duplicate of this bug. ***
Comment 18 brianjheisler@gmail.com 2023-04-18 14:12:59 UTC 
I wrote up Bug 154871 thinking that the problem described in Bug 154792 was a different issue (losing several digits of significance vs. a single count in the least significant digit*). *This is how I read it anyway.

(ROUNDDOWN(28147497671265.2,0) results in 28147497671300 instead of 28147497671265 (notice ...1300 vs ...1265, a two digit error of 35) and scaling up by x10 scales up the lost data by x10 (a three digit error).)

Reading the notes in Bug 154792 suggests that this subject can get a bit deep. I see this function has been modified and will be available with 7.6.0. For now, I'll shelve my observations and defer to the better-informed. When 7.6.0 is released, I will revisit and test.
Comment 19 Eike Rathke 2023-04-19 12:45:08 UTC 
Pending review https://gerrit.libreoffice.org/c/core/+/150505 for 7-5
Comment 20 Commit Notification 2023-04-20 07:51:56 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-5":

https://git.libreoffice.org/core/commit/f0f2ceaf978a0eee8e3c1fcfe6dec546ab8f07d6

Resolves: tdf#138220 tdf#154792 Avoid double rounding; tdf#124286 follow-up

It will be available in 7.5.4.

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.
Comment 21 NOYB 2023-09-04 08:50:54 UTC 
This has not been "resolved fixed".

Round down errors begin at 12 digits in LibreOffice Calc 7.6.  MS Excel exhibits no round down errors.

See screen capture image attachments of LOC and MSE.

Version: 7.6.0.3 (X86_64) / LibreOffice Community
Build ID: 69edd8b8ebc41d00b4de3915dc82f8f0fc3b6265
CPU threads: 8; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
Comment 22 NOYB 2023-09-04 08:54:21 UTC 
Created attachment 189328 [details]
LibreOffice Calc Round Down Errors Screen Capture
Comment 23 NOYB 2023-09-04 08:56:04 UTC 
Created attachment 189329 [details]
MS Excel Round Down No Errors Screen Capture
Comment 24 Eike Rathke 2023-09-04 10:33:46 UTC 
We do not state a precision higher than 12 significant decimal digits.
In fact this is on purpose here, see the change's source code comment:

// A quite aggressive approach with 12 significant digits.
// However, using 14 or some other doesn't work because other
// values may fail, like =ROUNDDOWN(2-5E-015;13) would produce
// 2 (another example in tdf#124286).

If Excel does not exhibit a round-off _in this case_ then fine for Excel. Otherwise see https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

Your screen captures tell nothing by the way, they're different numbers and no indication what actually "fails".

If you insist on reopening this bug again, I'm deassigning myself. If you want someone to entirely rewrite the numeric overall behaviour to implement arbitrary-precision arithmetic then this bug is not the place for that.
Comment 25 Eike Rathke 2023-09-04 10:52:12 UTC 
*** Bug 157082 has been marked as a duplicate of this bug. ***
Comment 26 b. 2024-05-09 20:59:25 UTC 
I seem to remember that a while ago someone tried to  
fix errors in rounddown and roundup by preceding them  
with a 'roundsig' to 12 significant digits. Looks as if  
Eike kept that in his patches, then you may be a victim  
of the mathematically questionable quality of such  
measures. 
 
Feeding known inaccurate results of subtractions 
- cancellation - into other formulas and then bending  
these to ( try to ) produce wanted results for some  
cases has - IMHO - similar quality.
Comment 27 b. 2024-05-10 14:41:19 UTC 
@erAck, you are sure about 8.23456789013 in the roundup  
unit-tests? Didn't run the tests, just looked into the  
code and consider them questionable, think 
8.23456789014 fits better.  
  
And about the concept, while you accept / provoke contradictions  
to 'math' in 'digits behind 12' you'll - IMHO - always have 
cases which fail 'left of that' as the devias propagate left  
in chains of '000...' or '999...' .  
  
And pls. don't answer it has to be like that, it's not a 
consequence of math, only partly a consequence of IEEE, and  
mostly resulting from Calc's combination of decisions and  
algorithms.