1. ## Rounding numbers (2002)

I am trying to round a lengthy column of numbers to 3 significant figures rather than a fixed number of decimal places. I have used the formula

=ROUND(A1,B1-1-INT(LOG10(ABS(A1))))

where A1 is the number to be rounded and B1is the number of sig figs. It works fine, except for numbers where the last significant digit is a zero to the right of the decimal. Can anyone suggest a way to display that last significant zero? A fixed format won't work since the number range from 0.00100 to over 1000.

Thanks

Mark

2. ## Re: Rounding numbers (2002)

You can use something like this in (for example) C1:
=FIXED(A1, B1 - 1- INT(LOG10(ABS(A1))))

but it will be text. If you need to use the values in C1 in calcs, you can then use in calculation (instead of C1), value(C1) to get the value of the text.

Other than that you would have to create a macro which changed the format whenever values in col A or col B changed.

Steve

3. ## Re: Rounding numbers (2002)

You might try thismacro I wrote some time ago.

<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>

4. ## Re: Rounding numbers (2002)

Jan and Steve -

Thanks for the suggestions. I like Steve's simple cure to the problem. I think I can live with text as long as it has that trailing significant zero.

Mark

#### Posting Permissions

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