Auto fill error

  • Thread starter Thread starter Nick_
  • Start date Start date
N

Nick_

if you fill say 500 rows with descending data from 1 to 500
and then the top adjacent row you place 1805, then the next row 1805.2
then 1805.4 then selce those three rows and then double click for excel
to autofill by the time its got to roww 110 its decided to stop
increment in values of 0.2 and adds in aanother fraction.

109 1826.80000000000
110 1827.00000000001

This has causes me big problems in the past as the new number is no
longer divisible by 0.2
 
It does the same thing if you select 1802, 1805.2, 1805.4 and drag them
down. The first error occurs at row 111, the 2nd at row 331 (200 rows on,
which must mean something). Well spotted.

Rgds,
Andy
 
Since 0.2 cannot be exactly represented in binary/floating point,
what you're seeing is the accumulated rounding error past the 15th
significant digit.

To work around this, you could enter 1805 in the first row, then

B2: =ROUND(B1+0.2,1)

and copy down - that way the rounding is done on each calculation
and doesn't accumulate. Then select the column and choose Edit/Paste
Special, selecting the Values radio button.

See http://cpearson.com/excel/rounding.htm for a more complete explanation.
 
Back
Top