# Thread: Round to 3 digits (Excel 2002)

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

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

3. ## 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?

4. ## Re: Round to 3 digits (Excel 2002)

Thank Hans,
This does the job well!
Cheers

5. ## 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)

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

7. ## 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?
cheers

8. ## 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, where-as only 3 to 4 are significant. Since the numbers range from 0.001 to 10000000, my code is very useful.

9. ## 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!
Cheers

#### Posting Permissions

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