Results 1 to 4 of 4
  1. #1
    New Lounger
    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,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. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    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

Posting Permissions

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