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
    Else
        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_func
@xl_arg('val', 'float')
@xl_arg('figs', 'int')
def py_SF(val, figs):
    if val == 0:
        return 0
    else:
        return np.round(val, figs-int(np.floor(np.log10(abs(val))))-1)

The comment from Larry Schuster links to VBA code at https://www.vertex42.com/ExcelTips/significant-figures.html
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 https://www.vertex42.com/ExcelTips/significant-figures.html
    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
            ' 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
    Else
        ' 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.
    https://www.vertex42.com/ExcelTips/significant-figures.html

    Like

  2. Calcpad says:

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

    Like

  3. Marco says:

    To get rounding to x significant digits in excel:

    =ROUND(num, -x)

    Like

    • 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

      Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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.