Rounding error for a value of 1.09

[Jeremy]

I am working with a spreadsheet that is using some references to values to
gather data. I take a number from a reference column and perform
manipulations on it to get the value I need to reference other fields. I
have no issues with any precision or rounding on values 1.01 - 1.08 (adding
..01 in each step). When I get to 1.09 instead of returning 9 as it should I
get 9.00000000000001. I confirmed that this happens in a new spreadsheet as
well.

Steps to reproduce:
Open a new excel workbook.
Enter "1.09" in cell A1
Enter "=A1*100" in Cell B1
Enter "=B1-100" in Cell C1
Enter "=C1*10000000000000000" in D1
D1 should now display "90000000000000100.00"

I know excel is only precise up to 15 characters. But I would expect to see
9 as the result of this manipulation. Does anyone have any idea as to why
this occurs with the number 1.09? This also happens between 1.48 and 1.99.
After 2.28 it gets even worse. I am keying these values with 2 point
precision, why is excel butchering the values at these intervals? I have
even attempted to increase the precision and this still happens. I believe
there is a defect that could skew data unpredictably.

Formulas starting at cell A1:
1 =A1*100 =B1-100 =C1*10000000000000000
=A1+0.01 =A2*100 =B2-100 =C2*10000000000000000
=A2+0.01 =A3*100 =B3-100 =C3*10000000000000000
=A3+0.01 =A4*100 =B4-100 =C4*10000000000000000
=A4+0.01 =A5*100 =B5-100 =C5*10000000000000000
=A5+0.01 =A6*100 =B6-100 =C6*10000000000000000
=A6+0.01 =A7*100 =B7-100 =C7*10000000000000000
=A7+0.01 =A8*100 =B8-100 =C8*10000000000000000
=A8+0.01 =A9*100 =B9-100 =C9*10000000000000000
=A9+0.01 =A10*100 =B10-100 =C10*10000000000000000
=A10+0.01 =A11*100 =B11-100 =C11*10000000000000000
=A11+0.01 =A12*100 =B12-100 =C12*10000000000000000
=A12+0.01 =A13*100 =B13-100 =C13*10000000000000000
=A13+0.01 =A14*100 =B14-100 =C14*10000000000000000

Formulas starting at cell A1:
1.00 100 0 0.00
1.01 101 1 10000000000000000.00
1.02 102 2 20000000000000000.00
1.03 103 3 30000000000000000.00
1.04 104 4 40000000000000000.00
1.05 105 5 50000000000000000.00
1.06 106 6 60000000000000000.00
1.07 107 7 70000000000000000.00
1.08 108 8 80000000000000000.00
1.09 109 9 90000000000000100.00
1.10 110 10 100000000000000000.00
1.11 111 11 110000000000000000.00
1.12 112 12 120000000000000000.00
1.13 113 13 130000000000000000.00

 

[kkknie]

Not sure exactly what you are trying to accomplish or why excel ha
problems with those numbers (probably a floating point issue), but yo
can just convert the first calculation to an integer and get rid of th
errors.

=INT(A1*100)

and copy down.

 

[swatsp0p]

Are you sure the formula in Cell C1 is correct as listed ("=B1-100)? You
are subtracting 100 from 109, which, of course, gives you 9. Hence 9 *
1(+16 zeros) results in your 900....100.00 figure. Now if you were to
multiply B1*100 in C1, then D1 returns an expected result of 109(+18
zeros).00

Bruce

 

[Frank Kabel]

Hi
also see:
http://www.cpearson.com/excel/rounding.htm

 

[Jerry W. Lewis]

As you will learn from Chip Pearson's article (recommended by Frank),
Excel (and almost all other general purpose numerical software) does
math in binary, not decimal. 1.09 has no exact finite binary
representation, and so must be approximated. The approximation Excel
uses is IEEE double precision (also used by almost all other general
purpose numerical software). The IEEE double precision approximation to
1.09 has a value of 4908923593833841/4503599627370496. When you
multiply by 100, the result again must be approximated, and the IEEE
double precision approximation to that value is
7670193115365377/70368744177664, which in decimal is
109.0000000000000142108547152020037174224853515625. Subtract 100 to get
633318697598977/70368744177664. When you multiply by 10000000000000000,
another IEEE approximation comes into play yielding 90000000000000144

You don't see the trailing figures (see Help for "Excel specifications
and limits" subtopic "Calculation specifications") because Excel does
not display more than 15 non-trivial digits, since 15 decimal digits is
roughly the limit of IEEE double precision. If you request more than 15
digits, Excel pads the display with trailing zeros, which neither
reflect nor alter the value actually stored. When you subtracted 100 in
cell C1, you brought the first digit of the approximation discrepancy
within that 15 digit limit. If you put =D1-90000000000000000 into E1,
Excel will display 144, indicating that Excel correctly performed all
the preceding calculations.

Obtaining the actual decimal values of the binary approximations is
nontrivial without specialized software, but a simple way to predict the
level of uncertainty introduced, is to use that 15 digit rule of thumb.
Your calculations are then viewed as
1.09000000000000??????
*100
--------------------
109.000000000000??????
-100
--------------------
9.000000000000??????
*10000000000000000
----------------------
9000000000000????.??
which is entirely consistent with the displayed result of
90000000000000100.00

Jerry