# Re: strange rounding of an arithmetic serie

• From: John Denker <jsd av8n com>
• To: gnumeric-list gnome org
• Subject: Re: strange rounding of an arithmetic serie
• Date: Thu, 30 Oct 2014 11:17:50 -0700

On 10/30/2014 10:33 AM, Frédéric Parrenin wrote:
OK I see.
But why this problem does not show up in Calc or XL?
It seems they manage to find a workaround.

There is no workaround in any XL or any other language
that uses floating-point arithmetic (double precision
or otherwise).

I guarantee that if you looked closely enough, you would
observe the same phenomenon in XL or any similar system.

On 10/30/2014 10:51 AM, Allin Cottrell wrote:
I don't know if these programs have an option "Show the number actually stored" but they ought to!

Try this, in cells A1 and A2:

=0.3+0.3+0.3+0.1
=A1-1                              [1]

You can "usually" /conceal/ the problem by rounding off
the display of the double-precision results.  If you
display few enough decimal digits, the rounded-off double
is "usually" indistinguishable from the rounded-off exact
answer ... although there are exceptions, as in example [1]
above, where no amount of rounding off the final answer
will conceal the problem.

To unconceal the problem in cell A1, display it using 20
decimal digits.

The root cause of the problem is purely mathematical.
Note the following 2x2 contrast:

1a) Numbers such as 1/2 and 1/5 can be represented as
finite-length decimal numerals.
1b) Numbers such as 1/3 and 1/6 cannot.  If you try,
you get a repeating decimal.

2a) Numbers such as 1/2 and 3/4 can be represented as
finite-length binary numerals.
2b) Numbers such as 1/5 and 1/10 cannot.  If you try,
you get a repeating binary numeral.

Do the calculation yourself, by hand, using long division,
using binary numerals:  Divide one-half by five like this:

____________________________________
101 ) 0.1000000000000000000000000000(...) [binary]