basic subtracting formula

  • Thread starter Thread starter Jeff Wittkopf
  • Start date Start date
J

Jeff Wittkopf

I was wondering why excel does this on certain numbers.
I'm trying to subtract two currency cells this is the
formula that I type in =R[-9]C-R[-3]C pretty basic
formula the thing that I don't get and it only does it on
certain decmail numbers is that for instance for the
first cell I had 67,953.65 and second cell at 57,953.65
it should equal to 10,000.00 but excel puts it at
9,999.9999999999999. I just don't understand why excel
wants to through it down to .9999999999 when it is a
whole number without any division. If someone knows the
reason for this could you please email me. I know that
we can fix the issue by using the ROUND function but it
shouldn't have to be that way

Thanks
Jeff Wittkopf
 
The references Peo supplied explain the issue of binary approximation.
For your particular problem, the IEEE approximations are

67953.64999999999417923390865325927734375
-57953.6500000000014551915228366851806640625

Do the math and you will see that Excel correctly calculates the exact
answer to this approximate problem to be
9999.9999999999927240423858165740966796875
which to 15 figures (the maximum number of significant figures that
Excel will display) is
9999.99999999999

You can roughly predict the range of effects for binary approximations
without actually determining the binary representations, by noting that
IEEE double precision fully resolves only 15 digits, thus your problem
is essentially
67,953.6500000000???
-57,953.6500000000???
----------------------
10,000.0000000000???
which is constant with the displayed answer of
9,999.99999999999

Jerry
 
Back
Top