Excel fill handle problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 (e-mail address removed)
 
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.
 
Dave Peterson said:
I think it's just the way binary systems deal with decimal numbers.
Click to expand...

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.
Click to expand...

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
 
Back
Top