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 doesnt 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:
http://support.microsoft.com/default...kb;en-us;78113
http://support.microsoft.com/default...b;en-us;214118
http://www.cpearson.com/excel/rounding.htm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill Owens" <Bill wrote in message
...
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.

You might find my functions at
http://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

"Bernard Liengme" wrote:

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:
http://support.microsoft.com/default...kb;en-us;78113
http://support.microsoft.com/default...b;en-us;214118
http://www.cpearson.com/excel/rounding.htm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill Owens" <Bill wrote in message
...
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.


--
rsenn
------------------------------------------------------------------------
rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050
View this thread: http://www.excelforum.com/showthread...hreadid=496970