+ Reply to Thread
Results 1 to 5 of 5

Excel cant count or I am crazy!

  1. #1
    Registered User
    Join Date
    07-18-2006
    Posts
    27

    Excel cant count or I am crazy!

    Using Excel 2003, if I sum the following numbers :

    -423,418.15
    -144,349.60
    -38,959.80
    -23,122.30
    -82.90
    31,225.79
    2,375.00
    10,160.83
    15,416.67
    22,145.83
    11,850.00
    6,442.08
    10,845.52
    25,411.50
    12,535.43
    5,333.35
    49,819.10
    18,882.17
    13,394.26
    13,823.57
    6,890.83
    7,307.50
    41,250.00
    13,900.91
    5,698.08
    13,771.68
    11,622.50
    4,334.16
    7,633.34
    5,916.66
    20,647.49
    39,471.96
    9,474.72
    14,963.33
    11,333.33
    6,201.67
    12,333.34
    3,916.67
    6,638.34
    2,703.84
    5,916.67
    3,025.66
    15,583.34
    3,166.68
    3,407.05
    5,041.67
    2,400.00
    1,954.17
    8,015.72
    5,615.40
    2,333.34
    10,833.33
    873.00
    2,566.50
    13,833.33
    5,911.66
    3,545.21
    18,153.95
    21,049.04
    145.55
    6,325.79
    -2,347.75
    -228.00
    -98.75
    -1,569.00
    468.72
    117.18
    346.84
    -4,000.00
    1,875.00

    I get a result of -2.30556906899437E-10!! I have copied them into notepad, and then copied them back in to clear all formatting, but I am still getting this bizarre result? I have tried them in different spreedsheets, I have manually inputted them.

    Help!
    Last edited by StevenAFC; 01-02-2009 at 08:49 AM.

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    The joys of rounding errors in excel and the conversion between binary and decimal!

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

    it should be 0, and is nearly that! You have to emply rounding somewhere in the calculations!

    Regards

    Dav

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    Yes Excel can count, and no you're not going crazy, just not interpreting what you're seeing. The sum of those numbers is zero. On my Mac it actually shows as zero, but it's not surprising that on some PCs it's displaying what you say - which is simply the best way it can find of telling you it's a very small number i.e.
    .00000000023055 etc

    I suspect one or more of those numbers are actually the result of a calculation, and because of the finite level of accuracy in any computer there is actually a very small residual number left when you add them up. You can prove this by using an =ROUND(A1,2) function. Put this in the adjacent column to your numbers and copy down, then sum this column. I'll be surprised if you don't get a big fat zero.

    HTH

  4. #4
    Registered User
    Join Date
    07-18-2006
    Posts
    27
    Well I am glad I am not crazy, I had already tried rounding the numbers, but unfortunatly it still doesnt work. But as long as I know its not me its fine, ill just have to Round the totals to make it work.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    Obviously rounding the total will work fine, but It seems strange, and I'm somewhat intrigued, to hear that you've rounded all the individual numbers and the unrounded total still shows as a small residual decimal. Do you want to upload the actual workbook so that we can take a look?

    Rgds

+ 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