Rounding to significant figures

Edited 19th Feb 22, following comment from Larry Schuster

Excel does not have a function to round numbers to a specified number of significant numbers, and (rather to my surprise) neither does Python. It is not too hard to write an on-sheet formula to do the job, but it is much more convenient to use a short user defined function (UDF):

Function SF(Value As Double, SigFigs As Long) As Double
Dim log10Val As Double, div As Double, Val As Double
       If Value = 0 Then
        SF = 0
        log10Val = Int(Log(Abs(Value)) / Log(10))
        div = (10) ^ log10Val
        Val = Value / div
        SF = (Round(Val, SigFigs - 1)) * div
    End If
End Function

In Python it can be even shorter, and using pyxll can also be called from Excel as a UDF:

@xl_arg('val', 'float')
@xl_arg('figs', 'int')
def py_SF(val, figs):
    if val == 0:
        return 0
        return np.round(val, figs-int(np.floor(np.log10(abs(val))))-1)

The comment from Larry Schuster links to VBA code at
which has additional error checks, and uses the same technique as the Python code. Note that for this to work when “sigs – (1 + exponent)” is negative the WorksheetFunction.Round function must be used, rather than the VBA Round function. This was the reason I used a different approach in my code. I have added the check for an input value of zero to both of my functions.

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

All three functions return the same results for valid input values, but the Vertex42 code gives more explicit error values when the input is non-numeric:

This entry was posted in Excel, Link to Python, Maths, NumPy and SciPy, PyXLL, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

7 Responses to Rounding to significant figures

  1. Larry Schuster says:

    This site has an extensive discussion of significant digit rounding and provides functions with error checking that yours doesn’t.


  2. Calcpad says:

    Hi, why not just use:
    k = 10^SigFigs
    SF = Int(Value * k) / k


  3. Marco says:

    To get rounding to x significant digits in excel:

    =ROUND(num, -x)


    • dougaj4 says:

      Did you try it?
      That rounds x places to the left of the decimal point, so:
      =ROUND(12345.6, -2) = 12300.0
      =ROUND(12345.6, -3) = 12000.0
      =ROUND(12.3456,-2) = 0.0


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.