+ Reply to Thread
Results 1 to 7 of 7

Sum formula rounds up to the next penny

  1. #1
    Registered User
    Join Date
    06-01-2014
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    3

    Sum formula rounds up to the next penny

    I have a sum formula that adds 3 different cells from 3 different columns to reach my total profit. The sum total will occasionally be rounded up one cent. I have check formatting and it is set to accounting with 2 decimal places. The formula is the same in each row as well as the format of each cell. Why would the formula occasionally round up? Help please!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,389

    Re: Sum formula rounds up to the next penny

    Hi and welcome to the forum

    Up to .004, it will round down, after that, it will round up. It is not actually rounding the number, it is just displaying it to that format, the underlying value remains unchanged

    The values on the left are formatted to accounting, the values on the right show the actual cell contents...
    J
    K
    4
    $ 1.22
    1.223
    5
    $ 1.22
    1.224
    6
    $ 1.23
    1.225
    7
    $ 1.23
    1.226
    8
    $ 1.23
    1.227
    9
    $ 1.23
    1.228
    10
    $ 1.23
    1.229
    11
    $ 1.23
    1.23
    12
    $ 1.23
    1.231
    13
    $ 1.23
    1.232
    14
    $ 1.23
    1.233
    15
    $ 1.23
    1.234
    16
    $ 1.24
    1.235
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,300

    Re: Sum formula rounds up to the next penny

    Excel maintains the maximum possible precision for real numbers, even if you are only displaying two decimal places. The amount displayed will be rounded to the nearest cent, but rounding errors can cause a SUM to round to a result that is different than if you add up what is displayed. Is that what you are seeing?

    The way to manage that is to explicitly round each individual amount from the 3 different cells.

    See attached for a simple example to illustrate the problem and solution.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    06-01-2014
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    3

    Re: Sum formula rounds up to the next penny

    Thanks for the quick replies!

    Yes, The amount displayed that is rounded to the nearest cent, is resulting in errors - a result that is different than if you add up what is displayed.

    I will try to round each individual amount from the 3 different cells.

    Thank you all!

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: Sum formula rounds up to the next penny

    you can also use the trunc funtion

    =trunc(yourformula,2) -> this will drop the other decimal retaining 2 decimal points only. no rounding down/up

    .122 -> .12
    .125 -> .12
    .129 -> .12
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  6. #6
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Sum formula rounds up to the next penny

    Quote Originally Posted by jacalynl View Post
    I have check formatting and it is set to accounting with 2 decimal places.
    Quote Originally Posted by jacalynl View Post
    Yes, The amount displayed that is rounded to the nearest cent, is resulting in errors - a result that is different than if you add up what is displayed
    Simply formatting to display 2 decimal places only affects the appearance of values. It does not affect the precision of the actual value. (Unless the "Precision As Displayed" option is set, which I do not recommend.)

    Moreover, most non-integers are not represented exactly, due to the way that Excel stores numbers and performs arithmetic. Consequently, small differences can creep into the results of arithmetic in surprising ways. For example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!).

    Consequently, you should explicitly round non-integer arithmetic when you expect the result to be accurate to a specific number of decimal places. Usually, that means using the ROUND function. For example, IF(ROUND(10.1 - 10, 1) = 0.1, TRUE) returns TRUE.

  7. #7
    Registered User
    Join Date
    06-01-2014
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    3

    Re: Sum formula rounds up to the next penny

    Quote Originally Posted by vlady View Post
    you can also use the trunc funtion

    =trunc(yourformula,2) -> this will drop the other decimal retaining 2 decimal points only. no rounding down/up

    .122 -> .12
    .125 -> .12
    .129 -> .12
    Thank you! I will try this!

+ 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. [SOLVED] ROUNDING Formula marking if it rounds numbers below x.5
    By drrazor in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-10-2014, 04:41 PM
  2. [SOLVED] Trying to create a formula that rounds up when its needed to.
    By Predictedizard in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-09-2013, 03:30 AM
  3. [SOLVED] I need a formula that rounds up the date.
    By VivatMartin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 08:44 AM
  4. Replies: 2
    Last Post: 01-17-2011, 11:55 PM
  5. [SOLVED] I need a %age increase formula that rounds up to the nearest $10
    By Chopper_Haynes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-04-2005, 02:05 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