Excel fill handle problem

Excel 2003 (11.6560.6568) SP2

I entered 5.75 in A1 5.8 in A2 5.85 in A3 then highlighted the
three cells. Then I dragged the fill handle down till the value was up to 32.

I pressed the Ctrl key and the Tilde key and the values that appeared had
become intermittently wrong.

some had 999999999999 values...some were correct...

Can someone explain why this error is happening and is there a fix?

please email me at john_french@adelphia.net

I think it's just the way binary systems deal with decimal numbers.

I'd put 5.75 in A1 and put =a1+.05 in A2 and drag down.

John wrote:
>
> Excel 2003 (11.6560.6568) SP2
>
> I entered 5.75 in A1 5.8 in A2 5.85 in A3 then highlighted the
> three cells. Then I dragged the fill handle down till the value was up to 32.
>
> I pressed the Ctrl key and the Tilde key and the values that appeared had
> become intermittently wrong.
>
> some had 999999999999 values...some were correct...
>
> Can someone explain why this error is happening and is there a fix?
>
> please email me at john_french@adelphia.net

--

Dave Peterson

"Dave Peterson" wrote:

> I think it's just the way binary systems deal with decimal numbers.

Specifically, most terminating decimal fractions (including 0.8 and 0.05)
are nonterminating binary fractions, that can only be approximated. The
difference in the binary approximations to 5.75 and 5.80 is
0.04999999999999982236431605997495353221893310546875 which Excel displays as
0.0499999999999998 per its documented display limit of 15 digits. Since the
increment is smaller than the OP intended, it is not surprising that
resulting partial sums are smaller than expected

> I'd put 5.75 in A1 and put =a1+.05 in A2 and drag down.

That would have a similar problem, since 0.05 still must be approximated.
The direct binary approximation to 0.05 is
0.05000000000000000277555756156289135105907917022705078125, which is a closer
approximation, but now some values will be too big instead of too small.

Better approaches would include
=ROUND(A1+0.05,2)
in A1 and copied down, or
=(114+ROW())/20
in A1:A526.

Jerry