Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    Athens, Ohio, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rounding in Excel 2003 (Excel 2003)

    I have a calculation that I want to show as currency. However, I want Excel to round starting with the 4th digit after the decimal. It appears to be rounding starting with the third digit.

    Any help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rounding in Excel 2003 (Excel 2003)

    I'm not sure I understand what you want. If you select the cell(s) with the result of the calculation, then select Format | Cells, and activate the Number tab, you can set the number of decimals to display. If that is not what you meant, please provide more info.

  3. #3
    New Lounger
    Join Date
    Oct 2004
    Location
    Athens, Ohio, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding in Excel 2003 (Excel 2003)

    I have a cell with a calculation result of 54.724500. I want two decimal places to show. The result I am looking for with this number is 54.73 by rounding from the fourth digit. Excel is rounding from the third digit and giving me 54.72. Obviously, the only time this is a problem is when the fourth digit is 5 or more and the third digit is 4 or more. This is a spreadsheet that must be easily explained to a layman and cannot be "suspect" because of manipulation to get the right result. Is there an easy way to do this. I have been presented with some possible solutions that were very complicated and involved a lot of steps. I would rather have to explain the rounding difference than a complicate fix.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rounding in Excel 2003 (Excel 2003)

    The "official" method for rounding to n digits is to look at the (n+1)-th digit. If the (n+1)-th digit is 5 or more, add 1 to the n-th digit, otherwise leave it alone.
    So if you round 54.7245 to 2 digits, you only look at the 3rd digit. Since this is a 4, the 2nd digit is left alone, and the number is rounded to 54.72. Note that you do NOT round in multiple steps (first round the 4 up to 5 because it is followed by a 5, then round the 2 up to 3 because it is now followed by a 5.
    This makes sense, for 54.72 is closer to 52,7245 than 54.73: the difference between 54.72 and 54.7245 is 0.0045, and the difference between 54.7245 and 54.73 is 0.0055.
    So if you rounded 54.7245 to 54.73, you'd have a much harder time explaining the result!

  5. #5
    New Lounger
    Join Date
    Oct 2004
    Location
    Athens, Ohio, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding in Excel 2003 (Excel 2003)

    The problem is, I am trying to show that my result matches someone elses result - and they rounded from the 5. There are a series of these numbers and this instance is the only one that does not match - again because of the 5 in the fourth place and the 4 in the third. We are talking about commissions taken out of a transaction. The company that figures the commission obviously wants to get as much money as possible. I don't agree with their calculation I just need to show that I can come up with the same number. Right now I can't. I am one penny off on only this transaction.

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

    Re: Rounding in Excel 2003 (Excel 2003)

    As Hans said, that method of rounding is definitely not correct. I think it would be better to point out the other persons error that make your results match the incorrect results. However, if you are determined to do the rounding incorrectly, then the following formula can be used to get the results you want if the value is in A1:

    <code>
    =ROUND(ROUND(A1,3),2)
    </code>
    Legare Coleman

  7. #7
    New Lounger
    Join Date
    Oct 2004
    Location
    Athens, Ohio, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding in Excel 2003 (Excel 2003)

    Thanks, that worked.

    I agree this is wrong but in this case there is no sense in arguing. Sometimes you just have to go on.

    Thanks for the help

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rounding in Excel 2003 (Excel 2003)

    The formula =54.73 would also have returned the desired incorrect result. There is no "correct" way to cheat! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding in Excel 2003 (Excel 2003)

    Well put, Hans!

  10. #10
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding in Excel 2003 (Excel 2003)

    Is it possible the "rounding" was done using the CEILING function which rounds up to an interval.


    =CEILING(A1,0.01)

  11. #11
    New Lounger
    Join Date
    Oct 2004
    Location
    Athens, Ohio, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding in Excel 2003 (Excel 2003)

    I don't know how the "other guy" did it. I was given a number and had to back into it.

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

    Re: Rounding in Excel 2003 (Excel 2003)

    I seriously doubt it. Using that function would also "round" 54.7235 to 54.73 which is not what was desired based on previous discussion.
    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
  •