Rounding to Significant Figures in Excel

value :: the number you wish to round.
sigfigs :: the number of significant figures you want to round to.

Syntax: ROUNDSIG(value,sigfigs)

Example: ROUNDSIG(-0.04589,2) equals -0.046

Function ROUNDSIG(num As Variant, sigs As Variant)
    Dim exponent As Double
    If IsNumeric(num) And IsNumeric(sigs) Then
        If sigs < 1 Then
            ' Return the  " #NUM "  error 
            ROUNDSIG = CVErr(xlErrNum)
        Else
            If num <> 0 Then
                exponent = Int(Log(Abs(num)) / Log(10#))
            Else
                exponent = 0
            End If
            ROUNDSIG = WorksheetFunction.Round(num, _
                       Sigs - (1 + exponent))
        End If
    Else
        ' Return the  " #N/A "  error 
        ROUNDSIG = CVErr(xlErrNA)
    End If
End Function

See Also: ""

To ensure that significant trailing zeros are displayed correctly when rounding a number to a certain number of significant digits, you need to work with text formats. The following formula is very confusing, but it gets the job done.

Update 4/19/2016: Fix for numbers such as 99.95 through 99.9999999 rounded to 3 SDs:

Note: If you are okay with leaving the number displayed in a scientific notation (e.g. 0.100E+003 for the number 100), then the formula can be as simple as this:

Another Note: You can convert a text number to a value by multiplying by 1 or adding 0 or using double negation, for example:

I got the idea to use the text format in this way from John McGimpsey's site. However, the above megaformula lets you choose any number of significant digits. Below is a VBA function using this method to round significant digits. The function returns the value as a string, so when using the value in other formulas, you can use VALUE(cell) to convert the string to a numeric value.

Function ROUNDSF(num As Variant, sigs As Variant) As String
    Dim exponent As Integer
    Dim decplace As Integer
    Dim fmt_left As String
    Dim fmt_right As String
    Dim numround As Double
    If IsNumeric(num) And IsNumeric(sigs) Then
        If sigs < 1 Then
            ' Return the   "  #NUM  "   error 
            ROUNDSF = CVErr(xlErrNum)
        Else
            numround = WorksheetFunction.text(num, "." & _
                        String(sigs, "0") & "E+000")
            If num = 0 Then
                exponent = 0
            Else
                'Round is needed to fix a ?truncation? 
                'problem when num = 10, 100, 1000, etc. 
                exponent = Round(Int(Log(Abs(numround)) / Log(10)), 1)
            End If
            decplace = (sigs - (1 + exponent))
            If decplace > 0 Then
                fmt_right = String(decplace, "0")
                fmt_left = "0."
            Else
                fmt_right = ""
                fmt_left = "0"
            End If
            ROUNDSF = WorksheetFunction.text(numround, _
                      fmt_left & fmt_right)
        End If
    Else
        ' Return the   "  #N/A  "   error 
        ROUNDSF = CVErr(xlErrNA)
    End If
End Function