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

Source: Eng-Tips; Engineering Spreadsheets

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.

### Like this:

Like Loading...

*Related*

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.

LikeLike

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

LikeLike

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.

LikeLike

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

http://www.jkp-ads.com/articles/round2digits.asp

LikeLike

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.

LikeLike