[FORMULA] Incorrect Sum Returned

[Alan B. Huynh]

Dear all:


I tried searching for this in the forum using the
keywords "formula, sum, addition, error" but couldn't find
any relevent information, so I'm posting it here. I'm
using a simple addition/subtraction SUM formula across
data arranged in columns, as such:

Cell A: 285,000.00
Cell B: 275,293.96
Cell C: 10,464.30
Cell D: =sum(A-B-C) [value is -758.26]
Cell E: -758.26
Cell F: =sum(D-E) [value SHOULD BE 0, but...]

When the cells are in accounting number format, Cell G1
returns the value of (0.00), which is a negative amount of
zero, instead of a simple dash (as zero values are notated
appear by default). When I put the numbers in General
format, I can see that Cell G1 returns the value of -
2.02363E-11.

The same thing happens a few rows down in my spreadsheet:

Cell A: 308,098.00
Cell B: 273,734.66
Cell C: 35169.80
Cell D: =sum(A-B-C) [value is -806.46]
Cell E: -806.46
Cell F: =sum(D-E) [value SHOULD BE 0, but...]

.... this time, it returns 0.00 in accounting format -- a
postive amount of zero. In general number format, the cell
value is 2.27374E-11

Can someone please explain this to me and show me how to
resolve this?

Thank you for your time,
-Alan B. Huynh
(e-mail address removed)

 

[Harald Staff]

Hi Alan

What you have here (2.2E-11) is in real life nothing, it's very very very
small. If you earn that amount each second of your life, day and night all
week through from your birth, you will own 0.01 , a whole cent, at the age
of 14 years plus. Nothing, really.

But it will cause problems if you by formulae or code test for =0
conditions.

The difference is probably caused by rounding errors going
decimal/binary/decimal. See
http://www.cpearson.com/excel/rounding.htm
for theory in this. What to do ? Either round or allow some slack around
your testing values.

HTH. Best wishes Harald

 

[Frank Kabel]

Hi
not an error. See
http://www.cpearson.com/excel/rounding.htm
for an explanation. Use ROUND to prevent this 'error'

 

[Dave R.]

Try searching for "rounding error", or click on

http://www.cpearson.com/excel/rounding.htm

for an explanation. Also, you don't need to use SUM(D-E), you can just use
D-E. SUM is for adding separate numbers together. The result of D-E, or
A-B-C-D, is considered one number, so doesn't have anything to sum with and
is not needed.

 

[Jerry W. Lewis]

As noted by others, this is not an error, but the result of number
conversion to binary. Of all the numbers in your problem, only cell A
(an integer) can be exactly represented in binary.

Since computers do binary math, most decimal fractions must be
approximated. What you get back is the exact answer to that approximate
problem. If you chase through the details at Chip Pearson's site
(address in previous replies) you would find that the correct answer
(using binary representations to the inputs) is
-89/4398046511104 = -2.0236257114447653293609619140625E-11
which is what Excel returned. Hence there is nothing wrong with Excel's
math (which incidentally is done by your Pentium, not by Excel); it
agrees perfectly with the IEEE standard.

If you don't want to chase through all the details of decimal-binary
conversion, a simple way to think about it is to note from Help on
"Excel specifications and limits - Calculation specifications" that the
precision of Excel (and almost all other computer software in the world)
is 15 digits. Think of your problem as
285,000
-275,293.960000000????
-10,464.3000000000???
+758.260000000000?
----------------------
0.000000000????
You got
-0.0000000000202363...
which is well within the 15 digit limitation of the IEEE binary
approximations.

As noted by others, the usual approach is to test for approximate rather
than exact equality, or else to round the final result before
comparison. Your inputs have nothing beyond the second decimal place,
and you are only doing addition/subtraction, so anything beyond the
second decimal place is residue from binary conversion; therefore round
the final result to 2 decimal places.

Jerry

Dear all:


I tried searching for this in the forum using the
keywords "formula, sum, addition, error" but couldn't find
any relevent information, so I'm posting it here. I'm
using a simple addition/subtraction SUM formula across
data arranged in columns, as such:

Cell A: 285,000.00
Cell B: 275,293.96
Cell C: 10,464.30
Cell D: =sum(A-B-C) [value is -758.26]
Cell E: -758.26
Cell F: =sum(D-E) [value SHOULD BE 0, but...]

When the cells are in accounting number format, Cell G1
returns the value of (0.00), which is a negative amount of
zero, instead of a simple dash (as zero values are notated
appear by default). When I put the numbers in General
format, I can see that Cell G1 returns the value of -
2.02363E-11.

The same thing happens a few rows down in my spreadsheet:

Cell A: 308,098.00
Cell B: 273,734.66
Cell C: 35169.80
Cell D: =sum(A-B-C) [value is -806.46]
Cell E: -806.46
Cell F: =sum(D-E) [value SHOULD BE 0, but...]

... this time, it returns 0.00 in accounting format -- a
postive amount of zero. In general number format, the cell
value is 2.27374E-11

Can someone please explain this to me and show me how to
resolve this?

Thank you for your time,
-Alan B. Huynh
(e-mail address removed)