Function SUM not working

1. Function SUM not working

Hello,
and sorry for my english.

I have a little problem with function SUM (excel 2003).

I have sum up five numbers:
1. 0,0006
2. -0,0008
3. 0,0002
4. 0,0000 or 0
5. 0,0000 or 0

...and SUM of these numbers is -8,13152E-20

What is wrong?

2. I'm no scientist or mathematician but it's something to do with this

http://en.wikipedia.org/wiki/Scientific_notation

3. Originally Posted by ksz
Hello,
and sorry for my english.

I have a little problem with function SUM (excel 2003).

I have sum up five numbers:
1. 0,0006
2. -0,0008
3. 0,0002
4. 0,0000 or 0
5. 0,0000 or 0

...and SUM of these numbers is -8,13152E-20

What is wrong?
Hi
right click on cell which contains SUM formula, click Format Cell... and under categoty list select Number and increase decimal numbers as required.

4. but the problem is not in scientific notaion but with these:

0,0006 + (-0,0008) + 0,0002 + 0 + 0 = 0 (zero) not -8,13152E-20

and I don't have special formating cell (it must be set as AUTO)

5. Originally Posted by ksz
but the problem is not in scientific notaion but with these:

0,0006 + (-0,0008) + 0,0002 + 0 + 0 = 0 (zero) not -8,13152E-20

and I don't have special formating cell (it must be set as AUTO)
do you want to show 0 (zero) at the begining of number.

6. Maybe I don't understand it

Originally Posted by starguy
do you want to show 0 (zero) at the begining of number.
but in these example I expected receive: 0, not -8,13152E-20 or -0,0000000000000000000813152

When I set:
0,02
0,1
0
-0,02
0
the SUM is: 0,1

When I set:
0
0
0
0
0
the SUM is: 0

but when I set:
0,0006
-0,0008
0,0002
0
0
the SUM is: -8,13152E-20 (not 0). Why?

7. Originally Posted by ksz
Maybe I don't understand it

but in these example I expected receive: 0, not -8,13152E-20 or -0,0000000000000000000813152

When I set:
0,02
0,1
0
-0,02
0
the SUM is: 0,1

When I set:
0
0
0
0
0
the SUM is: 0

but when I set:
0,0006
-0,0008
0,0002
0
0
the SUM is: -8,13152E-20 (not 0). Why?
the sum you show is bits 64 & 65 in binary counting from the decimal point. Chip Pearson explains counting problems at http://www.cpearson.com/Excel/rounding.htm

the binary of the 3 numbers you have are
0.0008 000000000011010001101101110001011101
0.0002 000000000000110100011011011100010111
0.0006 000000000010011101010010010101000110

and I see no reason why it doe not resolve to zero except that the problem I think of as the 10c rule applies, that is the numbers (like .1) have no definitive value, but but keep 'recurring' in a pattern ever decreasing in value.
Simply put, it's the same problem calculators had with one-third.

Perhaps chip's explanation will give you a much better understanding.

hth
---

8. the sum you show is bits 64 & 65 in binary counting from the decimal point. Chip Pearson explains counting problems at http://www.cpearson.com/Excel/rounding.htm

the binary of the 3 numbers you have are
0.0008 000000000011010001101101110001011101
0.0002 000000000000110100011011011100010111
0.0006 000000000010011101010010010101000110
Thanx for link. Maybe it is my problem.

I don't know like the MS Excel's SUM function working, but in my example SUM these three walues: 0,0006, -0,0008 and 0,0002 is 0, but when I have add next cells with 0 the SUM going wrong. 0+0+0=0?

9. As Bryan suggests, this is a known issue in Excel (and other applications). Here's what Microsoft says

http://support.microsoft.com/kb/78113/en-us

If you want your sum to resove to zero then, as suggested in the link, use ROUND, e.g.

=ROUND(SUM(A1:A5),10)

or similar

10. ...and it is the end.

Thank You for help me.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1