Doesn't add up correctly

[Allen]

Put .01 in cell A1, put =.01+A1 in cell A2 and copy down the formula to A250
so that you get .01 added every cell. Now click on the increase decimal
button until you can see out to 20 digits past the decimal point.

Question: Cell A82 through A99 - 1 in the 15th place shows up then
disappears. Anyone know why?

Question: At cell A230 the answer is suddenly an approximation of the true
answer. Again, why?

I know I can forcibly round this to be correct, but despite the numerical
closeness, I received erroneous data becuase I was grabbing the hundreds
place digit which was different because of the error. Given the simple
addition, I was caught off guard by this.

Thank you.

 

[Nick Hodge]

Allen

ALL computers work with whole numbers and any floating point number will
always be an approximation. The reason you see that at the 15th decimal is
that you have reached the limit of Excels precision

http://support.microsoft.com/default.aspx?scid=kb;en-us;214118

You can find many discussions on this by typing rounding errors in google.
'Error' perhaps or just the limitation of computer design. Certainly it
applies to ALL computers not just Excel

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Gary''s Student]

Rounding errors in floating point calculation. But you already know the
solution:

ROUND()


[don't be a square]

 

[Jerry W. Lewis]

Allen

ALL computers work with whole numbers and any floating point number will
always be an approximation.

This is only slight hyperbole. A few decimal fractions such as 0.5, 0.25,
0.75, 0.125, ... (powers of 2 and small sums of powers of 2) can be exactly
represented in binary; the vast majority are nonterminating binary fractions
that must be approximated in finite precision (just as 1/3 must be
approximated in decimal).

What the OP observed is the binary equivalent of
(2/3) + (2/3) - 1 = 0.6667 + 0.6667 - 1 = 1.3334
The final answer is not the same as the best 4 decimal place approximation
to 4/3, not because there is anything wrong with the additions or
subtractions, but because the initial approximations to finite precision
carried through the calculations causing the final result to be only
approximately correct.

The decimal representation of the binary approximation to 0.01 is
0.01000000000000000020816681711721685132943093776702880859375
hence accumulated sums are too large. The real action in A82:A99 is in the
16th decimal place, not the 15th, where the accumulated approximations exceed
5E-16 and therefore round to 1 in the 15th place. This effect does not
disappear as the OP supposes at A100; examine the result of
=(A100-1)
(the parentheses are required in the formula to prevent MS from muddying the
water by assuming that you want to see zero for this calculation).

You can more easily see what is going on behind the scenes using the VBA
functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465

Besides rounding, which is suggested in Nick's Knowledge Base link, for the
OP's type of chain calculation you could minimize the approximation involved
with scaled integer calculations, such as
=ROW()/100

Jerry

 

[Nick Hodge]

Jerry

Knew someone wold chime up with a more complete answer.... great work!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)