+ Reply to Thread
Results 1 to 10 of 10

Function SUM not working

  1. #1
    Registered User
    Join Date
    03-24-2007
    Posts
    8

    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. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    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

  3. #3
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Quote 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. #4
    Registered User
    Join Date
    03-24-2007
    Posts
    8
    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. #5
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Quote 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. #6
    Registered User
    Join Date
    03-24-2007
    Posts
    8
    Maybe I don't understand it

    Quote 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. #7
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote 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
    ---
    Last edited by Bryan Hessey; 03-24-2007 at 08:05 AM.
    Si fractum non sit, noli id reficere.

  8. #8
    Registered User
    Join Date
    03-24-2007
    Posts
    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. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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. #10
    Registered User
    Join Date
    03-24-2007
    Posts
    8
    ...and it is the end.

    Thank You for help me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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