SUM incorrectly calculates when summing positive and negative vals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Has anyone seen this problem?

Enter these numbers in an Excel spreadsheet:
9980.00
2620.00
-2414.10
-9381.20
-804.70
Then SUM(A1:A5) (assuming you entered the numbers in A1 to A5).
The given answer is incorrect. The value Excel gives is -0.0000000000011368...

Excel 2000 SP-3
Windows 2000 SP-4
AMD Athlon 64
1 Gb RAM

We have tried this on three computers (all same OS, same Excel): all gave
the same results. I tried this on a WinXP machine running OfficeXP, and still
the same (incorrect) answer.

Any thoughts?
 
this is a general comuting problem. You need to put in place processes to
manage the real world (rather than an idealised mathematical model).

When converting a decimal number that is not an integer into binary
representation, the conversion is not exact.
The binary representation may be slightly bigger than the decimal value, or
may be slightly smaller. However, these will often cancel out.

Your particular choice of numbers is such that the error in -9381.20 is
larger than the error in the other two negative numbers (because more bits
are needed to cope with the integer part, so fewer are available for the
non-integre part). This means that the rounding errors do not cancel and you
are left with a residual error.

The precision of an answer can never be greater than the precision of the
components (science 101), so you can avoid this artifact of your computing
platform by use of the ROUND function.

Mike.
 
Back
Top