View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Jerry W. Lewis
 
Posts: n/a
Default Extra 1 showing up in 15th decimal place

Specifically, the binary approximations (per IEEE 754 standard) to the
numbers in this calculation have decimal values of
9.32130000000000080717654782347381114959716796875
-9.1173000000000001818989403545856475830078125
--------------------------------------------------
0.20400000000000062527760746888816356658935546875
which is consistent with Excel's result of
0.204000000000001

You can predict the level of binary approximation effects by using
Excel's documented limit of 15 digits; think of the problem as
9.32130000000000???
-9.11730000000000???
--------------------
0.20400000000000???

Jerry

Bernard Liengme wrote:

This the result of the way computers (not just Excel) store numbers. We use
decimal numbers (we have 10 digits), computers use binary numbers (a switch
can be on or off). The IEEE convention allows computers to store about 15
decimal places. Some decimal values cannot be exactly represented in this
limit so we get 'round-off' errors.
Rather than test if (a-b) = 0.204, test if ROUND(a-b,3)=0.204
best wishes