Significant Figures

Excel does not provide any built in functions to round values to a specified number of significant figures.  The UDF below, recently posted at the Eng-Tips forum, will do the job:

Public Function sigfig(my_num, digits) As Double
Dim num_places As Integer
' Note that VBA log function returns natural logarithm.
' Dividing VBA log / log (10) converts to the base 10 logarithm.
' Screen out zero values to prevent error in logarithm calculation.
If my_num = 0 Then
sigfig = 0
num_places = -Int((Log(Abs(my_num)) / Log(10) + 1) - digits)
If num_places > 0 Then
sigfig = Round(my_num, num_places)
sigfig = Round(my_num / 10 ^ (-num_places)) * 10 ^ (-num_places)
End If
End If
End Function

Source: Eng-Tips; Engineering Spreadsheets

Screen shot

Screen shot

 To create the UDF:

  • Open the Visual Basic Editor (Alt-F11)
  • Create a new code module, if necessary (Insert-Module)
  • Copy and paste the code above into the module
  • Change all the characters to apostrophes (comment lines will change from red to green), or delete all the comments

Alternatively go to the Eng-Tips site and copy and paste from there, and there will be no need to correct the comment lines.



The John Walkenbach site gives the formula below:


 which works well when inserted as a formula, but doesn’t work inall cases as a UDF because the VBA Round function does not accept a negative number of decimal places, whereas the Excel Round function does.

This entry was posted in Excel, Maths, UDFs, VBA and tagged , , . Bookmark the permalink.

5 Responses to Significant Figures

  1. fzz says:

    Why do you need a udf? For n significant digits,


    with Transition Formula Evaluation disabled, or


    with it enabled.


  2. dougaj4 says:

    fzz – You don’t “need” one, but I find =sigfigs(x,n) much easier to remember than =VALUE(TEXT(x,”.”&REPT(”0″,n)&”E+000″), not to mention that it’s much quicker to type.

    Apart from which if using scientific notation I’d rather format it to the required precision and keep all the figures (and have a figure before the decimal point).


  3. fzz says:

    Shorter typing usually means longer recalc time. It’s a tradeoff between a few extra seconds of typing ONCE vs a few extra milliseconds recalc time MANY times. The deciding factor should be which is expected to amount to the most lost time. Unless sigfigs would be a novelty udf used very, very rarely, I think recalc time wins.

    It’s all a question of when to use udfs. For me, anything that can be done with 50 or fewer characters and 5 or fewer built-in function calls should never be done with a udf.

    As for your last point, the scientific notation used in the TEXT function ISN’T for display. It’s to make it possible to use n (the number of significant digits) in the formula directly without adjustment(s). You may prefer


    but that seems pointless to me.


  4. I have a small routine which does some rounding using number formats:


  5. dougaj4 says:

    fzz – I doubt if we will agree about when it is appropriate to use UDFs, but I take your point on the use of the scientific notation in the text function. For the benefit of others, if you copy and paste fzz’s first formula you will need to replace the leading symbol that looks like a long minus sign with two minus signs, and over-type the ” characters with quote characters from the keyboard that Excel will accept.

    JKP – thanks for the alternative.


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 )

Google+ photo

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

Connecting to %s