Apache OpenOffice (AOO) Bugzilla – Issue 88429
ROUNDDOWN((8.2-8)*10) returns 1
Last modified: 2009-03-28 14:01:14 UTC
ROUNDDOWN function is not working propertly: ROUNDDOWN((8.2-8)*10) returns 1 Note that this bug is affecting microsoft excel as well, but it seems impossible to report a bug to them.
Well, there is an explanation involving floating point operations on computers, but I do not like that explanation very much either. Floating point arithmetic was introduced many decades ago, and now we are in the 21st century, so I hope that more accurate methods will get implemented. The real problem is however slightly different. This issue determined me to analyse how well Calc behaves with floating point operations, and I must say that Calc fails with all decimals, while other programs fail only with some of them: in Clac: =ROUNDDOWN(10*(x - 8)), where x = 8.1 to 9.0 will always yield a number less than the expected value. In other programs (not tested in Excel), it fails only for: 8.1, 8.2 and 8.6 and 8.7 EXPECTED: 1 2 3 4 5 6 7 8 9 10 SEEN in R: 0 1 3 4 5 5 6 8 9 10 * in Calc: 0 1 2 3 4 5 6 7 8 10 ** * commands to compute in R (for comparison, ceiling is also provided) > floor(10*((8+1:10/10)-8)) [1] 0 1 3 4 5 5 6 8 9 10 > ceiling(10*((8+1:10/10)-8)) [1] 1 2 4 5 5 6 7 9 10 10 Expected: 1 2 3 4 5 6 7 8 9 10 ** see attached spreadsheet to see various combinations in Calc It seems that Calc stores even 8.5 as an inaccurate float number, when in fact it is possible to store it accurately. But, mostly I do hope that more powerful algorithms get developed and implemented. And that floating point handling will be better in the future.
Created attachment 53026 [details] TestCase with various float combinations
confirming it.
It may be related to Issue 86775.
> in Calc: > =ROUNDDOWN(10*(x - 8)), where x = 8.1 to 9.0 > > will always yield a number less than the expected value. Actually, I am wrong. I cannot reproduce the wrong result for every number. Calc fails only for some numbers, like 8.1 and 8.2. The matrix I have attached did demonstrate this. Calc performs extensive rounding internally, so that numbers close to integers (and sometimes even less close) are rounded to the integer value. This explains the accurate result even for 8.6 and 8.7, where other programs - even with greater precision than Calc, yield a different result. Calc's extensive rounding can be viewed both good and bad. I do not have a strong opinion (for statistics on large data sets, this may be detrimental, but for usual spreadsheet applications having an integer result is probably the better alternative). PROBLEM ======= The problem with my first calculation stems from the way Calc fills a series, see the 2nd spreadsheet attached. When using the automatic fill option, the resulting numbers (8.3 to 8.9) are computed wrongly and aren't quite the same numbers as when typed by hand. I think this is a bug in its own. I wasn't aware until now about this behaviour. It may make sense, but is very counterintuitive and creates a lot of trouble later, because most users won't suspect that these numbers are actually very different from the "should-be" numbers.
Created attachment 53120 [details] Calc Test Case showing peculiar behaviour
The error that mislead me during the initial testings is already described in another issue, see: http://www.openoffice.org/issues/show_bug.cgi?id=88119 [Incorrect decimal numbers when filling series]
added myself to cc-list .
Such problems are already tracked in issue 69749. *** This issue has been marked as a duplicate of 69749 ***
closing duplicate