Sum Function has small remainder
 

HI.

I am using the sum function to add three numbers that I know add to zero. I have typed these numbers in using the keyboard and none of these numbers have more than two decimal places. The numbers are

9,375.73
(9,362.27)
(13.46)

The answer given by the sum function is (0.00000000000087396756498492300000)

I am formatting all these numbers as comma style. In comma style all true zeros show up as "-" and all numbers between (.005) and .005 exclusively are displayed as "(0.00)" This causes inconsistencies in how zeros are displayed in my report.

I am using Excel 2003 on Windows 7

Thank you for your help

Michael

Sum Function has small remainder
 

"Hi I am Michael" wrote:
I am using the sum function to add three numbers that I know add
to zero. I have typed these numbers in using the keyboard and none
of these numbers have more than two decimal places. The numbers are
9,375.73
(9,362.27)
(13.46)
The answer given by the sum function is
(0.00000000000087396756498492300000)

This is not uncommon. Before I explain the problem, let me explain the
solution.

Whenever you expect arithmetic with non-integers to be accurate to a
specific precision, always explicitly round the result. For example, if
those numbers are in A1:A3, then:

=ROUND(SUM(A1:A3),2)

will avoid the problem.

Alternatively, you could set the calculation option "Precision as displayed"
(PAD) and format all of the cells as Number with 2 decimal places. But I do
not recommend PAD for several reasons.

The problem arises because Excel relies on the native computer binary
representation and arithmetic, the industry-standard 64-bit binary
floating-point.

Consequently, most non-integers cannot be represented exactly. And this
leads to infinitesimal differences when they are combined arithmetically.
Sometimes the differences cancel out; sometimes they compound and become
significant.

In your case, the following shows the exact representations internally:

9375.73 9375.72999999999,956344254314899444580078125
-9362.27 -9362.27000000000,043655745685100555419921875
-13.46 -13.4600000000000,008526512829121202230453491210937 5
=SUM(A1:A3) -0.000000000000873967564984923,22862148284912109375

If you use paper and pencil to add the numbers on the right, you would
probably get the result shown on the right in the last line.

Thank you for the explanation I will use the round function from now on.