# Rounding all numbers in the selection to a fixed number of digits

## Introduction

When doing analysis of measured data, it is customary to show the results in a fixed number of digits, called the significant digits. The number of digits depends on the accuracy of the measurement itself.

Whilst Excel has several methods to round your results, there is none that can handle rounding to a specified number of digits, one that is able to:

Round 1234567 to 1.23E+6

Round 1.234567 to 1.23

Round 0.001234567 to 0.00123

Of course you could use scientific notation (see screenshot), but in my experience many people (including executives reading your report) have a hard time understanding 1.23E-1, whereas understanding 0.123 is quite within reach.

Figure 1: Number format, Scientific notation

This is why I wrote this little subroutine, which rounds all numbers in the selection to the number of digits specified:

Dim dDigits As Double

Dim iCount As Integer

Dim iRoundDigits As Integer

Dim rArea As Range

Dim rCell As Range

Dim rRangeToRound As Range

Dim sFormatstring As String

Dim vAnswer As Variant

On Error Resume Next

Set rRangeToRound = Selection

If rRangeToRound Is Nothing Then Exit Sub

vAnswer = InputBox("How many digits?", "Rounding function")

If TypeName(vAnswer) = "Boolean" Then Exit Sub

If vAnswer = "" Then Exit Sub

iRoundDigits = CInt(Application.Max(1, vAnswer))

On Error GoTo 0

For Each rArea In rRangeToRound.Cells

For Each rCell In rArea

If IsNumeric(rCell.Value) And rCell.Value <> "" Then

sFormatstring = "0"

If rCell.Value = 0 Then

dDigits = 3

Else

dDigits = Log(Abs(rCell.Value)) / Log(10)

dDigits = -Int(dDigits) + iRoundDigits - 1

dDigits = Application.Min(Len(Abs(rCell.Value)), dDigits)

End If

If dDigits >= 1 Then

If Int(rCell.Value) = 0 Then

sFormatstring = sFormatstring & "." & String(dDigits - 1, "0")

Else

sFormatstring = sFormatstring & "." & String(dDigits, "0")

End If

ElseIf dDigits < 0 Then

sFormatstring = sFormatstring & "." _

& String(iRoundDigits - 1, "0") & "E+00"

End If

rCell.NumberFormat = sFormatstring

End If

Next rCell

Next rArea

End Sub

Listing 1: Code to change format of cells so they just show significant digits.

### Acknowledgements

Thanks to Matthew Adams Rasmussen for correcting a small bug regarding rounding e.g. 0.995 to 0.100 instead of to 0.10 when rounding to 2 significant digits.

## Comments

All comments about this page:

Comment by: Rick Rothstein (24-6-2009 13:19:34)

dDigits = Log(Abs(Round(rCell.Value, iRoundDigits - 1 _

- Log(Abs(rCell.Value)) / Log(10)))) / Log(10)

I'm thinking you may have a misplaced closing parenthesis on that line.

Comment by: Jan Karel Pieterse (25-6-2009 02:29:19)

Well spotted, I have updated the code to fix the error.

Comment by: Rick Rothstein (22-10-2009 12:57:52)

Dim Num As String

Dim Parts() As String

Num = Format(Value, "0.##############################e+0;;0")

Parts = Split(CStr(Num), "E", , vbTextCompare)

If CDbl(Parts(0)) = 0 Then

RoundSignificantFigures = 0

Else

RoundSignificantFigures = CDbl(Format(Parts(0), "0" & _

Left(".", -(Significance <> 0)) & _

String(Significance - 1, "0")) & "E" & Parts(1))

End If

End Function

Comment by: Jan Karel Pieterse (22-10-2009 23:37:53)

Thanks!

(sorry my system messes up the indenting of your code)

Comment by: Ricardo Hernández-Tablas (13-3-2015 22:04:09)

It's an interesting solution. But in VBA you could use the Format function, i.e.,

Format(value,"0.000")

Comment by: Jan Karel Pieterse (16-3-2015 09:39:33)

I'm afraid you misinterpreted the basis of this article.

It is code meant to round numbers to the same number of digits, NOT to the same number of decimals.

So with this code suppose you have these numbers,

12.3456

123.456

1234.56

and you know you have 3 significant digits. My code will result in:

12.3

123

1.23E3

## Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.