Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number rounding question (Excel 2003)

    I have a column of values that I am displaying as currency and showing 2 decimal points. In the case of one number, 582.365, it rounds up to $582.37 and with another number, 1736.559, it rounds up to $1736.56. The problem appears in another cell where those two figures are added. It LOOKS like the result should be $2318.93, but instead it's showing $2318.92...because it's adding the true figures (with several decimal points) and THEN rounding, rather than adding the rounded figures. How can I remedy this? Seems like I've used some function before in Excel but I don't use it often enough to remember what it is.

    Grazie mile! - Sat.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number rounding question (Excel 2003)

    There are a number of ways to fix this problem, depending on exactly what your sheet looks like. For illustration purposes, lets say those two numbers are in A1 and A2.

    1- You can explicitely force the rounding before the numbers are summed like this:

    <code>
    =ROUND(A1,2)+ROUND(A2,2)
    </code>

    2- You can select Options from the Tools menu and then click on the Calculation tab in the dialog box. Then put a check next to the "Precision as displayed" option. This will cause all values in the workbook to be automatically rounded to the precision that they are displayed. This will work as long as you do not need the extra precision in any calculations anywhere in the workbook. WARNING, the extra precision will be permanently lost for all constants that were entered with more precision than the format displays. Turning this option off will not get the precision back.

    3- If the two values you are adding are a result of another calculation, then you can use the Round() function in that calculation. For example, if A1 contains a formula like say:

    <code>
    =C1-D1
    </code>

    Then you could change that formula to:

    <code>
    =ROUND(C1-D1,2)
    </code>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •