SUM OF NUMBERS APPEAR INCORRECT?

[JEV]

EXCEL 2003

30,625.010000000000000000000000000000
(30,570.020000000000000000000000000000)

54.989999999998

Why does the sum of these 2 numbers yield the answer shown? The answer is
being compared to the following separate sum below and is yeilding a
different answer as well. The 2 numbers being added or subtracted are not
calculated- they're entered manually.

=15340-15285.01
54.990000000000

If the answer to the second equation is formatted out to more than 12 places
you get something less than the 54.99 I would have expected in both instances.

Appreciate your insight.

 

[Gary''s Student]

This is simple rounding error. If you require a precise calculate to two
decimal digits, then use the =ROUND() function.

 

[Bernard Liengme]

Excel, and most other computer apps, follow the IEEE convention to store
digital numbers (number to base 10 that we use because we have 10 fingers)
as binary numbers (numbers to base 2, because a compute memory state is
either ON or OFF). This results is round-off errors like the one you found.

Work around: =ROUND(A1-B1,12) to round to 12 places

Note that there is seldom any good reason to work with more than 12 decimal
places; there are few things even a scientist can measure with that
precision.


More details on IEEE 754:
Chip's clear explanation
http://www.cpearson.com/excel/rounding.htm

Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us

(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980

What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm

Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1

Others:
http://support.microsoft.com/kb/214118

http://docs.sun.com/source/806-3568/ncg_goldberg.html

 

[Jerry W. Lewis]

To make explicit the previous correct replies, the subtraction problem that
Excel gave the correct answer to is
30625.00999999999839928932487964630126953125
-30570.02000000000043655745685100555419921875
--------------------------------------------
00054.98999999999796273186802864074707031250

Your attempt to display 30 decimal places did not reveal this, because (as
documented in Help), Excel will display no more than 15 significant figures.
As you demonstrated, if you request more than 15 significant figures, Excel
will display the 15-figure value padded with meaningless trailing zeros. If
you are interested, you can use the VBA functions at
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5
to see what is really going on under the hood.

Since the math is correct, and the results are only surprising because most
decimal fractions must be approximated in binary (as noted by Bernard), then
you can safely round the results (as suggested by Gary's Student)

Jerry