Bug 124286 - ROUNDDOWN is inaccurate with decimal numbers
Summary: ROUNDDOWN is inaccurate with decimal numbers
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium minor
Assignee: Winfried Donkers (retired)
URL:
Whiteboard: target:6.3.0
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2019-03-23 10:56 UTC by Epo Nym
Modified: 2019-03-29 06:33 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
demo roundown ods (9.91 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-03-23 15:53 UTC, Oliver Brinzing
Details
demo_roundown xlsx (8.84 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-03-23 15:54 UTC, Oliver Brinzing
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Epo Nym 2019-03-23 10:56:43 UTC
Description:
The ROUNDDOWN function is imprecise in some cases involving decimal numbers. For example:

ROUNDDOWN(8,94;2) yields 8,94 (correct).
ROUNDDOWN(8,94-8;2) yields 0,93 (wrong).
ROUNDDOWN(7,94-7;2) yields 0,94 (correct).



Steps to Reproduce:
1.Let cell A1 =8.94-8
2.Note how the correct result is displayed (0.94).
3.Let cell B1 =ROUNDDOWN(A1;2)
4. Note the wrong result (0,93).

Actual Results:
Wrong result (0.93)

Expected Results:
Correct result (0.94)


Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
Comment 1 Oliver Brinzing 2019-03-23 15:52:11 UTC
(In reply to Epo Nym from comment #0)
> Description:
> The ROUNDDOWN function is imprecise in some cases involving decimal numbers.

i can reproduce this already with AOO 4.1.5.
and result is different from MS Excel 2016: 0,94

adding two attachments...
Comment 2 Oliver Brinzing 2019-03-23 15:53:56 UTC
Created attachment 150231 [details]
demo roundown ods
Comment 3 Oliver Brinzing 2019-03-23 15:54:16 UTC
Created attachment 150232 [details]
demo_roundown xlsx
Comment 4 Winfried Donkers (retired) 2019-03-24 09:06:15 UTC
I understand the cause and in a way it is unavoidable.
It is in most cases not possible with fractional numbers for the internal (binary) value to be exactly the same value as the (decimal) number.
That means that the binary representation of 0.94 could be something like 0.939999999999999 (figures are an example, not actual values). Rounding down the latter will always produce 0.93.
A similar problem will be visible with ROUNDUP for some values.

I will see if I can find a way to avoid or at least significantly reduce the above phenomenon. As I don't know yet if that will be possible, I am not assigning the bug report to myself yet.
Comment 5 Commit Notification 2019-03-28 22:19:21 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/edcbe8c4e02a67c74ec6f85f28899431dbfa0765%5E%21

tdf#124286 fix annoying rounding error.

It will be available in 6.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.
Comment 6 Winfried Donkers (retired) 2019-03-29 06:33:11 UTC
The patch fixes the issue for ROUNDDOWN as well as ROUNDUP (e.g. ROUNDUP(8.06-8;2).