Round2Digits

Up • Defined Names • Corrupt Files • Create Addins • Startup Problems • Chart an Equation • Show Picture • Round2Digits • Control Events • Custom Find • FormulaWrapper • Disable Events • Workbook Open Bug • WebQuery • Register UDFs • Undo With Excel VBA • Select a range (VBA) • Transpose Table • Docking VBE Windows • Excel 2007 Tables • Excel 2007 Tables (VBA) • Update An Addin • Addin Installation • Object Lister • Excel 2007 FileFormat • Catch Paste • Listbox AutoSize • Fix Links to UDFs

• •

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 rCell As Range
    Dim dDigits As Double
    Dim iRoundDigits As Integer
    Dim sFormatstring As String
    Dim iCount As Integer
    Dim vAnswer As Variant
    Dim rRangeToRound As Range
    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 = Application.Max(1, vAnswer)
    On Error GoTo 0
    For Each rCell In rRangeToRound.Cells
        If IsNumeric(rCell.Value) And rCell.Value <> "" Then
            sFormatstring = "0"
            If rCell.Value = 0 Then
                dDigits = 3
            Else
                dDigits = Log(Abs(Round(rCell.Value, iRoundDigits - 1 _
                          - Log(Abs(rCell.Value)) / Log(10)))) / Log(10)
                dDigits = -Int(dDigits) + iRoundDigits - 1
                dDigits = Application.Min(Len(Abs(rCell.Value)), dDigits)
            End If
            If dDigits >= 1 Then
                sFormatstring = sFormatstring & "." & String(dDigits, "0")
            ElseIf dDigits < 0 Then
                sFormatstring = sFormatstring & "." & String(iRoundDigits - 1, "0") _
                                & "E+00"
            End If
            rCell.NumberFormat = sFormatstring
        End If
    Next

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.

Feedback

Since you have managed to reach the end of this article, maybe you would care to write me a small message, expressing your opinion on this article?

Click here to write an email message to me (Maybe I'll add a feedback page with the feedback I get).