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?
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?
I'm no scientist or mathematician but it's something to do with this
http://en.wikipedia.org/wiki/Scientific_notation
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
HiOriginally Posted by ksz
right click on cell which contains SUM formula, click Format Cell... and under categoty list select Number and increase decimal numbers as required.
starguy
Tahir Aziz
PAKISTAN
https://www.facebook.com/businessexcel
__________________
Forum Rules (read before you post)
Links to the world of Excel
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.Originally Posted by ksz
Maybe I don't understand it
but in these example I expected receive: 0, not -8,13152E-20 or -0,0000000000000000000813152Originally Posted by starguy
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.htmOriginally Posted by ksz
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
---
Last edited by Bryan Hessey; 03-24-2007 at 08:05 AM.
Si fractum non sit, noli id reficere.
Thanx for link. Maybe it is my problem.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
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?
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
...and it is the end.
Thank You for help me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks