Results 1 to 12 of 12

20060207, 18:31 #1
 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?

20060207, 19:02 #2
 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.

20060207, 19:15 #3
 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.

20060207, 19:29 #4
 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 nth 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!

20060207, 19:35 #5
 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.

20060207, 19:42 #6
 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

20060207, 19:49 #7
 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

20060207, 20:18 #8
 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>

20060207, 22:53 #9
 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!

20060208, 03:23 #10
 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)

20060208, 13:16 #11
 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.

20060208, 15:55 #12
 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