# Thread: Rounding in Excel 2003 (Excel 2003)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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>

7. ## 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. ## 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. ## Re: Rounding in Excel 2003 (Excel 2003)

Well put, Hans!

10. ## 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. ## 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. ## 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.

#### Posting Permissions

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