Results 1 to 4 of 4
Thread: Rounding numbers (2002)

20040513, 11:50 #1
 Join Date
 May 2004
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
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,B11INT(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

20040513, 12:16 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040513, 12:16 #3
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 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

20040513, 12:35 #4
 Join Date
 May 2004
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
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