Re: strange rounding of an arithmetic serie

[John Denker <jsd av8n com>]

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]