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:
Newton Excel Bach, not (just) an Excel Blog 
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
LikeLike
Thanks Larry. I have added your link to the post.
LikeLike
Hi, why not just use:
k = 10^SigFigs
SF = Int(Value * k) / k
LikeLike
Calcpad – that rounds to the specified number of decimal places rather than significant figures, and it always rounds down, so 0.999 to 2 significant figure would return 0.99, rather than 1.0
LikeLiked by 1 person
Yes, now I see, thank you. 🙂
LikeLike
To get rounding to x significant digits in excel:
=ROUND(num, -x)
LikeLike
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
LikeLike