Precision problem with automated formula fill-in

[Erny Meyer]

I start with a value of -1 in cell A1 of a worksheet. In
cell A2 I enter the formula =A1+0.1

I repeat the formula downwards. At cell A11 (where I
should read 0), I find an approximation -1.4E-16

Changing the formula to =(A1*10+1)/10 (which is basically
the same) eliminates the problem.

Can this type of problem be avoided through a setup option?

 

[JE McGimpsey]

You could use Tools/Options/Calculation Precision as displayed... Note
that that is a global setting. Or you could change your formula to

=ROUND(A1+0.1,1)

The problem is that 0.1 cannot be represented exactly in finite binary
digits, much as 1/3 can't be represented exactly in finite decimal
digits (e.g., 0.3333333...)
See

http://cpearson.com/excel/rounding.html