addition error-excel bug???

[bg]

In Excel2000, I ahve the follwing numbers I am trying to add:

-30584.010000000000000
30585.600000000000000
1.590000000000150


why does adding these two amounts add a 150 on the right end. I am inputting
to 2 decimals then formatting to add the zeros. My answer should be
1.590000000000000.

Any ideas???

 

[Myrna Larson]

All floating point, base-10 numbers cannot be represented exactly when
converting to base-2 binary format. You realize you can't represent the
fraction 1/3 exactly in base-10. 1.59 must fall into this category -- can't be
represented exactly in base-2.

 

[Jerry W. Lewis]

Myrna is correct, but more to the point, neither .01 nor .60 can be
represented exactly. Since both of the OP's inputs must be
approximated, it should be no surprise that the output is only approximate.

bg, the easiest way to think about these issues is to recognize that
Excel's documented limit of 15 decimal digit accuracy relates to the
accuracy of decimal/binary conversion. Thus your problem can be thought
of as
-30584.0100000000?????
30585.6000000000?????
----------------------
1.5900000000?????
which is consistent with your result of
1.59000000000015

The binary approximation to 30584.01 is 8406868654754365/274877906944
The binary approximation to 30585.6 is 4203652855313203/137438953472
The exact difference between these numbers is 437055872041/274877906944
which in decimal is 1.59000000000014551915228366851806640625, or
1.59000000000015 to 15 figures.

In keeping with Excel's documented limit of 15 decimal digit accuracy,
if you ask for more than 15 decimal digits, the trailing digits are
arbitrarily displayed as zero.

Jerry

 

[Myrna Larson]

Thanks for the additional info, Jerry.

Out of curiosity, what tool/program did you use to calculate those fractions
and long decimals?

 

[Jerry W. Lewis]

You're welcome. I use function that I wrote in Maple.

Jerry