Issue 88429 - ROUNDDOWN((8.2-8)*10) returns 1
Summary: ROUNDDOWN((8.2-8)*10) returns 1
Status: CLOSED DUPLICATE of issue 69749
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.4.0
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2008-04-18 10:04 UTC by reminodet
Modified: 2009-03-28 14:01 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
TestCase with various float combinations (9.50 KB, application/vnd.oasis.opendocument.spreadsheet)
2008-04-19 13:05 UTC, discoleo
no flags Details
Calc Test Case showing peculiar behaviour (11.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2008-04-22 19:58 UTC, discoleo
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description reminodet 2008-04-18 10:04:07 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.
Comment 1 discoleo 2008-04-19 13:01:47 UTC
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.
Comment 2 discoleo 2008-04-19 13:05:22 UTC
Created attachment 53026 [details]
TestCase with various float combinations
Comment 3 kyoshida 2008-04-20 14:55:55 UTC
confirming it.
Comment 4 kyoshida 2008-04-20 14:56:31 UTC
It may be related to Issue 86775.
Comment 5 discoleo 2008-04-22 19:56:21 UTC
> 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.
Comment 6 discoleo 2008-04-22 19:58:26 UTC
Created attachment 53120 [details]
Calc Test Case showing peculiar behaviour
Comment 7 discoleo 2008-04-23 19:39:25 UTC
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]
Comment 8 discoleo 2008-04-23 19:44:24 UTC
added myself to cc-list
.
Comment 9 Regina Henschel 2009-03-28 13:59:56 UTC
Such problems are already tracked in issue 69749.

*** This issue has been marked as a duplicate of 69749 ***
Comment 10 Regina Henschel 2009-03-28 14:01:14 UTC
closing duplicate