+ Reply to Thread
Results 1 to 11 of 11

How can the sum formula possibly add 12 extra decimal places?

  1. #1
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Mac Office 365
    Posts
    171

    How can the sum formula possibly add 12 extra decimal places?

    I am trying to get the actual total of a column of numbers using the sum formula, which I use all the time, and have never had this problem before.
    There is not a single number in the column that has more than 2 decimal places, yet somehow the sum formula has added 12 extra decimal places into the answer... I've tried using the round formula, I've tried adding the numbers in a new workbook, I've tried retyping all the numbers, I've separated out the decimal values & confirmed that is no more than 2 digits in all numbers, but the answer still seems to be wrong.
    Anyone know how I can fix this problem?
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.97.2 for Mac MS 365
    Posts
    8,708

    Re: How can the sum formula possibly add 12 extra decimal places?

    I admit I don't fully understand your problem.
    I looked at column A and all the #s above row 60 were formatted as general and had a lot of zeros to the right of the decimal point. But formatting as number with 2 decimal places corrected that. Same with the sum. is that what you are looking for?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.97.2 for Mac MS 365
    Posts
    8,708

    Re: How can the sum formula possibly add 12 extra decimal places?

    is the issue that your sum isn't 5.92 but rather 5.919999... and you don't know why since all the #s above seem to end at no more than 2 decimal places?

  4. #4
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Mac Office 365
    Posts
    171

    Re: How can the sum formula possibly add 12 extra decimal places?

    No... What I'm looking for is how can adding up #'s that have a maximum of 2 decimal places possibly add up to a # with 12 decimal places. It's not mathematically possible. The formatting is like that to show that the numbers being added only have max 2 decimal places, while the added column has 14 decimal places. Formatting it to 2 decimal places does not give me the correct answer, the answer still has 14 decimal places whether you can see them or not.

  5. #5
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Mac Office 365
    Posts
    171

    Re: How can the sum formula possibly add 12 extra decimal places?

    yes, post #3 is my issue

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How can the sum formula possibly add 12 extra decimal places?

    you cant short of using
    =ROUND(SUM(A3:A60),3)
    or
    maybe
    =SUMPRODUCT((A3:A60*100))/100
    its to do with precision and the way excel stores those decimals
    http://support.microsoft.com/kb/78113
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How can the sum formula possibly add 12 extra decimal places?

    It's an issue with Floating Point Precision (google it, it's a documented issue in Excel)

    Solution is to round the sum
    =ROUND(SUM(A3:A61),2)

    Change the 2 to whatever decimal is an acceptable level of accuracy for your purpose.

  8. #8
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: How can the sum formula possibly add 12 extra decimal places?

    This article may help to explain:

    rounding errors in floating point arithmetic
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  9. #9
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Mac Office 365
    Posts
    171

    Re: How can the sum formula possibly add 12 extra decimal places?

    that would work in my table part... any suggestions on how to manipulate that in my end-result pivot table? I didn't mention that part because I though the problem could be fixed at the source, sorry

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.97.2 for Mac MS 365
    Posts
    8,708

    Re: How can the sum formula possibly add 12 extra decimal places?

    can you just format the sum to two decimal places? wouldn't that carry through to the pivot table? And I know you can format the pivot table results to two or other decimal places.

  11. #11
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Mac Office 365
    Posts
    171

    Re: How can the sum formula possibly add 12 extra decimal places?

    Thank you ConneXionLost, that article helped, fixed it at the source

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adusting decimal places based on greatest number of places in a series
    By anelson87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2022, 01:05 PM
  2. Number showing Extra decimal Places in formula bar
    By dzap79 in forum Excel General
    Replies: 8
    Last Post: 09-30-2020, 03:20 AM
  3. Paste two decimal number in excel without extra decimal places appearing
    By jeffery_frick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2009, 07:49 PM
  4. decimal places within a formula
    By Paul01 in forum Excel General
    Replies: 4
    Last Post: 06-03-2005, 03:05 PM
  5. Drop extra decimal places
    By DejaVu in forum Excel General
    Replies: 1
    Last Post: 04-28-2005, 04:16 PM

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