software

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.

This roundoff error results from the way computers (it is not limited to
Excel) store numbers. The IEEE convention requires the decimal number to be
converted to binary with a fixed number of digits. Within this limit some
decimal numbers do no have an exact binary representation.
For more read:
support.microsoft.com/default...kb;en-us;78113
support.microsoft.com/default...b;en-us;214118
www.cpearson.com/excel/rounding.htm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Bill Owensquot; lt;Bill gt; wrote in message
...
gt; Why does
gt; 1.00000000000000000000-0.99950000000000000000=0.000499999999999945
gt; instead of 0.0005? I have 50,000 records that are evenly spaced by 0.0005
gt; and
gt; they appear that way in the column even when viewed with 20 places,
gt; however,
gt; after subtracting one number from the other the difference is not 0.0005.
gt; I
gt; tried using round off to 4 places and that doesn't resolve the problem.
gt; Any
gt; help is appreciated.

Try setting the Precision As Displayed (Tools gt; Options gt; Calculation gt;
Precision As Displayed) and then use a number format with 4 decimal
places.--
rsenn
------------------------------------------------------------------------
rsenn's Profile: www.excelforum.com/member.php...oamp;userid=29050
View this thread: www.excelforum.com/showthread...hreadid=496970You might find my functions at
groups.google.com/group/micro...fb95785d1eaff5
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

quot;Bernard Liengmequot; wrote:

gt; This roundoff error results from the way computers (it is not limited to
gt; Excel) store numbers. The IEEE convention requires the decimal number to be
gt; converted to binary with a fixed number of digits. Within this limit some
gt; decimal numbers do no have an exact binary representation.
gt; For more read:
gt; support.microsoft.com/default...kb;en-us;78113
gt; support.microsoft.com/default...b;en-us;214118
gt; www.cpearson.com/excel/rounding.htm
gt; best wishes
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Bill Owensquot; lt;Bill gt; wrote in message
gt; ...
gt; gt; Why does
gt; gt; 1.00000000000000000000-0.99950000000000000000=0.000499999999999945
gt; gt; instead of 0.0005? I have 50,000 records that are evenly spaced by 0.0005
gt; gt; and
gt; gt; they appear that way in the column even when viewed with 20 places,
gt; gt; however,
gt; gt; after subtracting one number from the other the difference is not 0.0005.
gt; gt; I
gt; gt; tried using round off to 4 places and that doesn't resolve the problem.
gt; gt; Any
gt; gt; help is appreciated.