## Rounding to significant figures

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:

This entry was posted in Excel, Link to Python, Maths, NumPy and SciPy, PyXLL, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

### 7 Responses to Rounding to significant figures

1. Larry Schuster says:

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

Like

• dougaj4 says:

Like

Hi, why not just use:
k = 10^SigFigs
SF = Int(Value * k) / k

Like

• dougaj4 says:

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

Liked by 1 person

Yes, now I see, thank you. 🙂

Like

3. Marco says:

To get rounding to x significant digits in excel:

=ROUND(num, -x)

Like

• dougaj4 says:

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

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.