Column Fill error? or Excel bug or processor bug?

Question

Hi all, not sure if this is a known bug, a new one, or just an error with my system, I'd be grateful if someone could have a go and see if they get the same, and/or know of a fix.

Put the value 1.001 in cell A1

Put the value 1.002 in cell A2

Highlight both cells and then do a drag fill down to a value of say 2

on my system all is well up until and including 1.046

after this point excel starts adding a few extra decimal places

What should be 1.047 comes out as 1.04700000000001

these extra 0's and a 1 continue until 1.14 which then becomes 1.14000000000002

This continues probably ad infinitum.

Any help would be appreciated.

Thanks

K

PS I'm using windows 7 with an i5 2500K

Answer 1

It's neither an error nor a bug. It's an artifact of XL (as most other spreadsheets) using double-precision floating point numbers to store values.

Most fractional amounts (including those represented as decimals) can't be exactly represented in a fixed number of binary digits , any more than most fractional amounts can be represented in a fixed number of decimal digits (e.g., 1/3 = 0.333333333....).

What you're seeing is the small rounding error in the last binary digit accumulating during the auto filling of the range.

See http://www.cpearson.com/Excel/rounding.htm for a fuller explanation.

Instead of autofilling, if you want to prevent that accumulation, use

A2: =A1 + 0.001

and copy that down.

Answer 2

Thanks for the info,

I'll have a look at the full explanation later

Thanks again

K