Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Round2Digits
Deze pagina in het Nederlands

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
    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 (6/24/2009 1:19:34 PM)

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 (6/25/2009 2:29:19 AM)

Hi Rick,

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

 


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

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 (10/22/2009 11:37:53 PM)

HI Rick,

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

 


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

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 (3/16/2015 9:39:33 AM)

Hi Ricardo,

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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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