Home > Article index > Round2Digits

# 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:

Sub RoundToDigits()
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
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
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.

Comment by: Rick Rothstein (24-6-2009 13:19:34) deeplink to this comment

I get a "Run-time error '5': Invalid procedure call or argument" on this line in your code...

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) deeplink to this comment

Hi Rick,

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

Comment by: Rick Rothstein (22-10-2009 12:57:52) deeplink to this comment

I'm not sure why I didn't post this sooner, but here is a shorter piece of code (in the form of a function) which provides significant digit rounding that I developed in response to a newsgroup question awhile back (thought your readers would be interested in seeing it)...

Function RoundSignificantFigures(Value As Variant, Significance As Long) As Double
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) deeplink to this comment

HI Rick,

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

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

Hi

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) deeplink to this comment

Hi Ricardo,

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

Comment by: Josep L. (14-1-2021 12:15:00) deeplink to this comment

Hi Jan,

I was looking exactly for a solution like yours. So, first of all, thank you again for sharing the code, I always learn from you.
I have tested your code and it worked for the majority of the numbers I thrown in, with the exception of positive numbers between 0 and 1. In such scenario, the shown digits are always 1 less than required (see 2 last examples below).

Complete    Formatted to 4 sigfig.

1.2345     1.235
-0.12345    -0.1235
1.2345     1.235
99.9        99.90

0.12345     0.123
0.98765     0.988

Am I missing something here? Is there something I can do to fix it?

Comment by: Jan Karel Pieterse (14-1-2021 12:52:00) deeplink to this comment

Hi Joseph,

This is due to the test in the middle of the code where I check whether the Integer of the value equals zero:

If Int(rCell.Value) = 0 Then
sFormatstring = sFormatstring & "." & String(dDigits - 1, "0")
Else
sFormatstring = sFormatstring & "." & String(dDigits, "0")
End If

If you replace that bit with just this, all values are treated the same:

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

Comment by: Josep L. (14-1-2021 16:41:00) deeplink to this comment

Hi Jan,

Thank you for quick fix, now it does what I expected. But I wonder, what was the reason for you to prefer one digit less if the integer is 0?

Comment by: Jan Karel Pieterse (14-1-2021 17:45:00) deeplink to this comment

Hi Joseph,

To be honest, I have no idea!

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

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].