Basic Subtraction Error

  • Thread starter Thread starter smccarvi
  • Start date Start date
S

smccarvi

I am subtracting a few numbers and getting a tiny error in my result It is
happening in multiple (but not all) places on my spread sheet. I don’t know
if this is a bug in Excel or something else.

This is an example of the error:

1,640.659
- 574.408
- 814.790
- 56.587
- 38.808
- 4.950
- 207.703

= -58.69999999998

The result is off by .0000000000002. Is this something I can ignore or do I
need to be concerned. Any way to fix?
 
It will be because the numbers you are summing are the results of other
calculations and although they may be formatted to x places, Excel will
store tem to 15 decimals. When you sum, this full precision is used rather
than the formatted number.

To test, take the formatting off the other numbers or expand the number of
decimals to see the 'real' precision Excel is using.

You can correct this by

a) Using the ROUND function
b) Turning 'precision as displayed

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
 
The math is correct, but most terminating decimal fractions are
non-terminating binary fractions that can only be approximated (just as 1/3
can only be approximated as a decimal fraction). When you do math with
approximate inputs, it should be no surprise when you get an approximate
result.

Since the issue is initial approximations instead of math accuracy, and
addition/subtraction mathematically does not inject new significant decimal
places, you can safely follow Nick's suggestion and round the result.

None of your input numbers have exact binary representations. At least one
of your input numbers is likely the result of some other calculation, since
had they been input directly, the result would have been -56.5869999999997
instead of your reported -58.69999999998

Jerry
 
Back
Top