Results 1 to 9 of 9
Thread: Round to 3 digits (Excel 2002)

20050510, 11:24 #1
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Round to 3 digits (Excel 2002)
Hi,
I've gone blank...I know it should be simple, but I can't think now!
I want to format numbers as R000Regards,
Rudi

20050510, 11:42 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Round to 3 digits (Excel 2002)
Use (for example) a custom format <code>0,</code> to display thousands, and <code>0,,</code> to display millions.

20050510, 12:21 #3
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Round to 3 digits (Excel 2002)
Lets be difficult:
Is it possible to display in ten thousands...the user wants 3 digits! I can use =ROUND(A1,5) to round to 123000.00 but it still shows as 12340 when I use 0, and 12 when I use 0,,
I want to see 123.
Any ideas?Regards,
Rudi

20050510, 13:26 #4
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Round to 3 digits (Excel 2002)
Thank Hans,
This does the job well!
CheersRegards,
Rudi

20050510, 13:40 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Round to 3 digits (Excel 2002)
I don't think you can do that with a number format. Does this formula do what you want? (Use General format for the result)
=ROUND(A1/100,0)

20050510, 13:47 #6
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Round to 3 digits (Excel 2002)
I've devised this little macro to round the selection to the specified number of digits:
<pre>Sub RoundToDigits()
Dim rCell As Range
Dim dDigits As Double
Dim iRoundDigits As Integer
Dim sFormatstring As String
Dim iCount As Integer
Dim vAnswer As Variant
Dim rRangeToRound As Range
On Error Resume Next
Set rRangeToRound = Selection
If rRangeToRound Is Nothing Then Exit Sub
vAnswer = InputBox("How many digits?", "Rounding function")
If TypeName(vAnswer) = "Boolean" Then Exit Sub
If vAnswer = "" Then Exit Sub
iRoundDigits = Application.Max(1, vAnswer)
On Error GoTo 0
For Each rCell In rRangeToRound.Cells
If IsNumeric(rCell.Value) And rCell.Value <> "" Then
sFormatstring = "0"
If rCell.Value = 0 Then
dDigits = 3
Else
dDigits = Log(Abs(rCell.Value)) / Log(10)
dDigits = Int(dDigits) + iRoundDigits  1
dDigits = Application.Min(Len(Abs(rCell.Value)), dDigits)
End If
If dDigits >= 1 Then
sFormatstring = sFormatstring & "." & String(dDigits, "0")
ElseIf dDigits < 0 Then
sFormatstring = sFormatstring & "." & String(iRoundDigits  1, "0") & "E+00"
End If
rCell.NumberFormat = sFormatstring
End If
Next
End Sub
</pre>
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20050510, 13:55 #7
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Round to 3 digits (Excel 2002)
The result gives a scientific number. ie 58387036.19 becomes 584E+07?
Must I format after the macro runs?
cheersRegards,
Rudi

20050510, 14:02 #8
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Round to 3 digits (Excel 2002)
The code is intended to format to a number of significant digits, but to keep showing the actual value.
So 1234567 will not be shown as 123 but rather as 1.23E7
I know this is not what you needed, but I thought the code might come in handy.
I use this code frequently with measurements that come out of an automated system, showing lots of digits, whereas only 3 to 4 are significant. Since the numbers range from 0.001 to 10000000, my code is very useful.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20050510, 14:04 #9
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Round to 3 digits (Excel 2002)
Thanx Pieterse. The code is handy and will always be!
I can provide it to the user as another option which I'm sure will be appreciated!
CheersRegards,
Rudi