+ Reply to Thread
Results 1 to 6 of 6

Currency 2 decimals and SUM is wrong!

  1. #1
    Registered User
    Join Date
    01-26-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    55

    Currency 2 decimals and SUM is wrong!

    HI I am trying to solve this mystery.
    I am including a little example and as you can see the Total SUM is not correct by 0.01.
    It is not a big problem but it looks bad as it appears we cannot do a simple addition.
    Why is the simple SUM wrong????

    Thanks for any help....
    BTW love this forum...learned ALOT!

    Regards,
    Rob

    Attachment: book1.xlxs
    Attached Files Attached Files
    Last edited by Rober; 01-05-2011 at 02:02 PM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Currency 2 decimals and SUM is wrong!

    The actual answer is
    209.4643

    Try
    =ROUNDUP(SUM(H2:I5),2)

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Currency 2 decimals and SUM is wrong!

    Welcome to the world of Excel and precision math. This one gets a lot of people.

    Simple answer, always employ a ROUND() function with your disivion functions to avoid these penny errors that crop up. This is because what is displaying to you is not the exact value IN the cell. The exact value goes out many decimal places and those extra decimal places can add up hidden and create pennies you don't expect.

    So, in H2 you have:
    =IF(C2="","",(C2*D2)/100)

    The portion in red is the division function, slip it into a ROUND() function to make the value exactly 2 decimals:
    =IF(C2="","",ROUND((C2*D2)/100,2))

    By fixing the rounding in your source cells, the SUM() functions will be what you visually expect.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Currency 2 decimals and SUM is wrong!

    for more info. on the issue see another current thread: http://www.excelforum.com/excel-gene...istencies.html

  5. #5
    Registered User
    Join Date
    01-26-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    55

    Re: Currency 2 decimals and SUM is wrong!

    Thank you for your replies. This really got me! I realized that Excel does not round up (or down) if not told to do so. And Davesexcel is correct "The actual answer is 209.4643".
    I used the formula below and I used the round function!
    Thank you so much for your help!
    I will read the link posted here for more info!

    R




    Quote Originally Posted by JBeaucaire View Post
    Welcome to the world of Excel and precision math. This one gets a lot of people.

    Simple answer, always employ a ROUND() function with your disivion functions to avoid these penny errors that crop up. This is because what is displaying to you is not the exact value IN the cell. The exact value goes out many decimal places and those extra decimal places can add up hidden and create pennies you don't expect.

    So, in H2 you have:
    =IF(C2="","",(C2*D2)/100)

    The portion in red is the division function, slip it into a ROUND() function to make the value exactly 2 decimals:
    =IF(C2="","",ROUND((C2*D2)/100,2))

    By fixing the rounding in your source cells, the SUM() functions will be what you visually expect.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Currency 2 decimals and SUM is wrong!

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ 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