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