View Single Post
  #5   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

As Bill Martin has noted, these numbers have no exact representation in
binary.

The IEEE double precision approximation to 0.365 is
1643813863990231/4503599627370496 which in decimal is
0.364999999999999991118215802998747676610946655273 4375.

The IEEE double precision approximation to 0.3575 is
6440147467139809/18014398509481984 which in decimal is
0.357499999999999984456877655247808434069156646728 515625

Do the math, the correct difference between these approximate inputs is
-0.007500000000000006661338147750939242541790008544 921875 which Excel
correctly reports (to its documented 15 figure limit) as
-0.00750000000000001

The simplest way to think about this is to recognize that digits beyond
the 15th figure may be unexpected due to binary approximation so your
problem becomes
0.357500000000000??
-0.365000000000000??
--------------------
0.007500000000000??
consistent with Excel's reported value of
0.00750000000000001

When you are adding numbers that have no more than 4 decimal places,
then anything beyond the 4 decimal place in the final result is binary
junk. Therefore simply round to 4 decimal places to eliminate binary
junk without doing violence to the computation.

Jerry

Sean wrote:

Actually I found this problem recently and don't know why this could happen.
I did simple minus calculation of these two values: (-0.365)-(-0.3575)
and excel shows -0.0075000000000000100000000.
It is not exactly -0.0075.
Do I need to change some settings or ....?

Thank you