What is the roundoff error in the subtraction function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Why does 1.00000000000000000000-0.99950000000000000000=0.000499999999999945
instead of 0.0005? I have 50,000 records that are evenly spaced by 0.0005 and
they appear that way in the column even when viewed with 20 places, however,
after subtracting one number from the other the difference is not 0.0005. I
tried using round off to 4 places and that doesn’t resolve the problem. Any
help is appreciated.
 
Try setting the Precision As Displayed (Tools > Options > Calculation >
Precision As Displayed) and then use a number format with 4 decimal
places.
 
You might find my functions at
http://groups.google.com/group/micr..._frm/thread/9f83ca3dea38e501/6efb95785d1eaff5
to be useful, since they can be used to display more than 15 decimal digits
of the internal representation of numbers. The internal representation of
0.9995 is 0.999500000000000055067..., so the result of your subtraction will
be slightly less than 0.0005 ...

As Bernard has noted, most decimal fractions (including 0.9995 and 0.0005)
cannot be represented exactly in binary, and hence must be approximated.
When you do math with approximate inputs, it should come as no surprise when
the result is only approximate.

A similar thing happens in the VBA currency data type (4 decimal places),
where
CCur(4 / 3) - Cur(2 / 3)
returns 0.3334 instead of 0.6666 instead of 0.6667. The reason why is
probably more intuitively obvious here, but it is basically the same thing.

Absent the use of my functions, you can use Excel's documented 15 digit
limit to think about your math problem as
1.000000000000000 (integers are exactly representable)
-0.999500000000000???
----------------------------
0.000500000000000???
which is consistent with Exel's answer of
0.000499999999999945

I do not understand the OP's complaint about rounding to 4 places and that
not resolving the problem; in Excel
=ROUND(1-0.9995,4)
returns 0.0005 (or more precisely, 0.00050000000000000001 which is as close
as 0.0005 can be approximated in IEEE double precision).

Jerry
 
Back
Top