=SUM() inconsistency, Can someone explain this to me?

  • Thread starter Thread starter DaveMoore
  • Start date Start date
D

DaveMoore

I have a column of figures with a sum function at the bottom.
I agree this total with a cell in another part of the spreadsheet using the
formula =H107=Sheet2!C100 this gives a value of TRUE or FALSE. The
FALSE value prompts me to investigate, and there have been no problems for
more than a year.
However, this month, on investigating a FALSE value find that on copying and
special pasting the cell containing the =sum formula to a value only I get
the answer 403.190000000001
I have checked each cell within the sum formula and no other cell contains a
value to 12 decimal places, indeed they are all to 2 decimal places.
Why has this happened?
What can I do to correct it? Perhaps change the formula to
=ROUND(SUM(H6:H106),2)

My thanks for any explanations offered.
Dave Moore
 
Hi Dave
change your formula as you have done in your post. This is not an error
but due to Excel's (and all other program's) internal representation of
numbers
 
Frank correctly noted that the solution is to round.

As to why, almost all computers and computer programs do math in binary.
Most exact decimal fractions must be approximated in binary (much as
1/3 can only be approximated in decimal). These binary approximations
are accurate to 15 decimal digits (documented in Excel Help).
Subtraction can sometimes reveal the approximations; for instance 24.2 -
24.1 can be thought of as
24.2000000000000??
-24.1000000000000??
-------------------
0.0000000000000??
which helps to understand why most computer programs (including Excel)
report the answer as
0.0999999999999979
It is not a bug or an inaccuracy in the math, it is the exact answer to
an approximate problem.

If you know that no inputs involve digits beyond the second decimal
place (as in dollars and cents), then any difference between answer and
ROUND(answer,2) is a residue of the binary approximations to the input
numbers.

Jerry
 
The discrepancy comes from Excel translating the base 10 numbers that
we use into the base 16 (hexadecimal) numbers that it uses for its
internal calculations. When the base 16 results get translated back,
sometimes these small extra values appear. So your solution of using
a ROUND function is best to use in these cases.
 
Back
Top