## 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 Else num_places = -Int((Log(Abs(my_num)) / Log(10) + 1) - digits) If num_places > 0 Then sigfig = Round(my_num, num_places) Else sigfig = Round(my_num / 10 ^ (-num_places)) * 10 ^ (-num_places) End If End If End Function ```

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.

Update:

The John Walkenbach site gives the formula below:

=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))

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,

=–TEXT(x,”.”&REPT(0,n)&”E+000″)

with Transition Formula Evaluation disabled, or

=VALUE(TEXT(x,”.”&REPT(“0″,n)&”E+000”)

with it enabled.

Like

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).

Like

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

=VALUE(–TEXT(x,”0.”&REPT(“0″,n-1)&”E+000”))

but that seems pointless to me.

Like

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