Number subtraction problem

  • Thread starter Thread starter Ashby Hawse
  • Start date Start date
A

Ashby Hawse

Hi,
I have a question for anyone that may have knowledge on a
problem that I am having. If I do the following In cell A5
enter a value of 867.22, in cell B5 enter a value of
864.11, in cell C5 enter the following formula =A5-B5.
Then change cell C5 formating to show up to 14 decemal
places. I get a value of 3.11000000000001 , how is this
possible?
 
Excel's arithmetic is exact, but your math problem can only be approximated.

See Chip's page for an in-depth explanation. The short answer is that
neither 867.22 nor 864.11 can be exactly represented in binary, and thus
have to be approximated. When you use approximate inputs, it should be
no surprise that the output is also approximate.

IEEE double precision resolves 15 significant decimal digits, so the way
to think about how Excel and almost all other computer software compute
this problem is

867.220000000000??
-864.110000000000??
--------------------
3.110000000000??
which Excel reports as
3.11000000000001

The exact answer based on IEEE double precision binary approximations to
your inputs is
3.1100000000000136424205265939235687255859375
hence my opening statement that the math is correct.

Since you input only 2 decimal places and are only doing
addition/subtraction, anything beyond the second decimal place is
residue of the binary approximations to the inputs. Thus J.E.
McGimpsey's recommendation to round is appropriate.

Jerry
 
Back
Top