LibreOffice Calc 7.5.2.2
Formula: =ROUNDDOWN(726498.789999828;2)
Expected Result: 726498.78
Actual Result: 726498.79
Is it a bug or am I doing something wrong?
I am curious why you want to round down at all, instead of simply setting the format of the number to 2 decimal places.
In your example, the mathematically true answer is much closer to xx.79 than to your “expected” result of xx.78.
Are you also curious as to why both Calc and Excel have rounding functions?
Essentially that’s what you’re asking.
A simple web search should be able to answer your question.
“Why does Excel have ROUNDDOWN function”
Format is for display. Rounding is for calculations.
I think you will find with experience that calculations should be done in general with as many significant digits as you have. Otherwise, you will end up with avoidable rounding errors that propagate through your calculation, and that lead to possibly larger errors in your final result.
Perhaps I sound harsh, but It seems to me that forcing a ROUNDDOWN or a ROUNDUP is just an accounting trick used by marketing experts.
To return to your example, the “expected” result would be in error by 0.009999828 while the result of rounding to the nearest two decimal places would be 0.000000172. The first is about 58 thousand times larger than the second. You can’t build rockets with errors like that.
There are 7 widget models being produced. The mfg. tracks widget completion state in 10% increments.
The bean counters calculate a dollars and cents value for the days total completed widgets.
The completed widgets metric of each model must be rounded DOWN to the nearest integer.
Rounding to nearest or up could result in an incorrect $ value. (We all know that money talks. Even though it can’t sing and dance and it can’t walk.)
Talk with a major financial institution and you’ll probably find lots of cases for rounding down. And likewise, a lot of cases for rounding up and even to nearest. There are valid uses for all of them. Even if we disagree with the usage.
Accounting on sheets requires rounding, otherwise you never get a balance due to the floating point arithmetic done in the background. On the other hand, accounting on sheets is a good reason to get fired.
I’m not seeking workarounds. I’m seeking correct operation of the round down function.
Function Wizard
ROUNDDOWN( Number, Count )
Rounds a number down to a predefined accuracy.
The ROUNDDOWN function does not reliably do what it claims. It is flawed. Period.
Not being able to do something as simple as truncating >nth digit is a good reason not to be a sheets developer. It is also a good reason for businesses and professionals not to use LibreOffice Calc.
Hi boss! Who are you? I must have missed something.
Do you know anything about the background, in specific about the relation between the internal representation of numbers and their decimal representation?
Do you actually believe your “alternative software” has no bugs?
Not understanding anything of the kind is a serious reason not to use software.
This is a bug. It is related to this FAQ, but in this case, it is possible to improve the calculation (after the rounding, we can simply check if the result is greater than the initial value, and correct it). Please file a bug report on it.
As long as the bug isn’t fixed expressions like
=INT($A$1)+ROUNDDOWN($A$1-INT($A$1);2)
should provide a reliable workaround.
(Ah… There was already suggested a workaround by @schiavinatto.)
Fwiw, that’s tdf#154792.
Unfortunately that’s only theoretically.
Looks like a bug, as @mikekaganski also confirms.
Workaround:
Can you use the FLOOR() function instead? It seems to be more reliable(but it is not; disregard my workaround; see (*)). With that, you give the rounding precision as “step size” instead of the count of decimals:
=FLOOR(726498.789999828;0.01)
Edit: I must have overlooked the other workarounds given when I first suggested this. Those other ways are just as good. They all circumvent the seemingly reduced precision used internally in the ROUNDDOWN function, by either moving significant digits aside during the process, or using a different function (or both).
Using the INT() with multiply/divide is the “old school” way which will work in every spreadsheet app known to mankind.
If you need other levels of precision (or dynamic precision) some approaches/workarounds are better than others. It often depends on context and what kind of intermediate results you are working with. Take care not to lose significant value!
(*) please note: I have not tested ROUNDDOWN() vs FLOOR() extensively to determine reliability. FLOOR() seems to have similar issues with other values. Other helpers here have more intimate knowledge of the inner workings and development than I do. See comment from @erAck et.al.
In this case. For the dreaded 8.94-8 case FLOOR(8.94-8;0.01) yields 0.93
(and no, that won’t change).
And Excel does the same 
As explained in tdf#154792, this was intentional, introduced in tdf#124286, and was intended to compensate the normal floating-point inaccuracies, like the previous =ROUNDDOWN(8.94-8;2) producing 0.93 instead of the expected 0.94, because the negation 8.94-8 produced 0.9399999999999995. So the rounding to 12 significant digits was introduced before rounding up/down in commit edcbe8c4e02a67c74ec6f85f28899431dbfa0765 (when the requested number of decimals was less than 12, indeed).
@erAck has improved this in this night’s commit 8186a01f2a26f05645a2a3c9c93b453bd35b796f. All the samples in the report (both OP’s, and my comment 1 there) now behave as expected.
However, it must be noted that the problem can’t be resolved cardinally, because the original problem of tdf#124286 still needs addressing. Despite OP’s claims, Excel also has the inaccuracies, like here:
It also rounds up in ROUNDDOWN when appropriate. Spreadsheets, despite being not exact, are widely used in accounting and similar areas, for which they had been tailored extensively; and in these areas, a deviation after a dozen of decimals is usually a sign of FP inaccuracies, rather than a significant information, which has to be accounted for to provide expected value. What spreadsheet software is not designed to is infinite accuracy.
Note also that OP’s claim in the bug:
Microsoft figured it out long long time ago. You’re decades behind on something as fundamental as subtraction and rounding down.
is plain wrong. One can try other examples, where we calculate much more accurate. E.g., having A1:A60 with 0.1 in each, =SUM(A1:A60) would give the expected 6.0 in Calc, but 5,99999999999999 in Excel (one might need to increase shown decimals to see that); and the ROUNDDOWN to 1 decimal on this sum would produce 5.9. Thanks to Dante, who implemented Kahan summation algorithm (or, more precisely, Neumaier’s) in tdf#137679.
Or putting 100.01 to A1, 100 to A2, selecting A1:A2, and dragging down to row 10002 (down to the expected 0) would result in -5,09885E-11 in A10002 in Excel, but 0 in Calc; summing absolute deviations from the correct values in all the cells A1:A10002 would produce ~4,40E-11 in Calc, but ~2,56E-07 in Excel (so total error is four orders of magnitude larger). And thanks to @erAck, this issue is also improved/fixed.
1 Like
What value does Excel round down incorrectly? Give me a value. Not a calculation that returns an incorrect value (ie. floating point error).
Appears you are passing floating point errors into the ROUNDDOWN function and then blaming ROUNDDOWN for the errors.
Like when you cite 0.93 result of ROUNDDOWN(8.94-8,2) on the ROUNDDOWN function. When in fact the ROUNDDOWN function correctly rounded down the value it was given.
The problem in that case is the 8.94-8 subtraction yielding an incorrect value such as 0.9399999999999995 (floating point error). Instead of the correct result of 0.94.
Rounding that value down to 2 digits is correctly 0.93.