A Rounding We Will Go

Mike Wolfe -

Rounding is a very simple concept in math.  Most of us learned it in elementary school.  Anything less than 0.5 gets rounded down; anything 0.5 or higher gets rounded up.  This is known as arithmetic rounding.

Arithmetic Rounding vs. Banker's Rounding

It turns out arithmetic rounding is not the only rounding game in town.  There is another form of rounding, known as banker's rounding or, simply, round-to-even. This rounding exists to account for arithmetic rounding's tendency to overinflate its estimates.

The problem becomes obvious if you step back and think about it for a minute.  The number 1.5 is just as close to 1 as it is to 2.  And yet, we always round it up to 2.  Likewise, the number 2.5 is just as close to 2 as it is to 3.  But again, we always round it up to 3.  Over a large enough sample of numbers, our estimates will be too high if we use arithmetic rounding.

Banker's rounding addresses this issue by saying, "Let's take every other number and round it down instead of up."  So, 1.5 and 2.5 would both round to 2, the closest even number (hence the term, "round to even").

Below is a quick example of the problem of arithmetic rounding and the solution of banker's rounding.  I'm adding up the numbers 0.5 plus 1.5 et cetera up to 9.5.  The unrounded sum is 50.  The arithmetic-rounded sum is 55.  Meanwhile, the banker's-rounded sum is exactly 50.  This contrived example is pretty close to a worst case, but the difference in the two forms of rounding is striking.  Clearly, banker's rounding has its uses.

'--== Original Addition ==--
?.5 + 1.5 + 2.5 + 3.5 + 4.5 + 5.5 + 6.5 + 7.5 + 8.5 + 9.5
 50 


'--== Traditional (Arithmetic) Rounding ==--
? 1  +  2  +  3  +  4  +  5  +  6  +  7  +  8  +  9  + 10
 55 


'--== Banker's Rounding (Round to Even) ==--
? 0  +  2  +  2  +  4  +  4  +  6  +  6  +  8  +  8  + 10
 50

VBA.Round() - Banker's Rounding

The Round() function is included in the VBA specification as part of the standard library.  Interestingly, the VBA specification says nothing about whether this function should be implemented using arithmetic or banker's rounding:

Runtime Semantics

§ Returns a number rounded to a specified number of decimal places.

The specification leaves this choice as an implementation detail.  The Office VBA reference is clear that it implements Round() using banker's rounding:

Note

This VBA function returns something commonly referred to as bankers rounding. So be careful before using this function. For more predictable results, use Worksheet Round functions in Excel VBA.

VBA itself is a programming language specification.  While most users and developers are only familiar with the Office VBA implementation, several other non-Microsoft software companies have implemented VBA (at least partially) into their own applications.

Arithmetic Rounding in VBA

The Office VBA Round() function uses banker's rounding.  We've discussed the advantages of that approach.  The one drawback--and it's a big one--is that very few people expect rounding to work that way.  So, we often find ourselves in need of a function to perform the more familiar arithmetic rounding.

The following function is a drop-in replacement for the VBA Round() function.  It uses the Format() function to perform the arithmetic rounding.  Note that the $ after the function name indicates that the function should return a String variable rather than a Variant.

Since the function matches the name of the original VBA library function, it will override that function anywhere you have a call to Round().  You can access the original VBA Round() function by using a fully qualified call, VBA.Round().  Do note, however, that you will not be able to do this from within an expression.  If you go this route, you may want to use the BankersRound() function which is nothing more than a wrapper around the built-in VBA.Round() function.

Source Code

The lines that begin with '>>> in the comments are doc tests.

'>>> Round(1.5)
' 2
'>>> Round(2.5)
' 3
'>>> Round(3.125, 2)
' 3.13
Function Round(Number As Variant, _
               Optional DigitsAfterDecimal As Long = 0) As Variant
    If IsNull(Number) Or IsMissing(Number) Then
        Round = Number
        Exit Function
    End If
    
    Select Case VarType(Number)
    Case vbInteger, vbLong, vbByte
        Round = Number
#If Win64 Then
    Case vbLongLong
        Round = Number
#End If
    Case vbDouble, vbSingle, vbCurrency, vbDecimal, vbString
        Dim NumFormat As String
        NumFormat = "#." & String$(DigitsAfterDecimal, "#")
        Round = Val(Format$(Number, NumFormat))
        
        'Force the result back into a specific type 
        '   to mimic the VBA.Round() function
        Select Case VarType(Number)
        Case vbDouble: Round = CDbl(Round)
        Case vbSingle: Round = CSng(Round)
        Case vbCurrency: Round = CCur(Round)
        Case vbDecimal: Round = CDec(Round)
        Case vbString: Round = CDbl(Round)
        End Select

    Case Else
        'This is a custom function that is part of my error handling module;
        '   you can use an Err.Raise statement in its place
        Throw "Unsupported variable type: {0}", VarType(Number)
    End Select
End Function

'>>> BankersRound(1.5)
' 2
'>>> BankersRound(2.5)
' 2
'>>> BankersRound(3.125, 2)
' 3.12
Function BankersRound(Number As Variant, _
                      Optional NumDigitsAfterDecimal As Long = 0) As Variant
    BankersRound = VBA.Round(Number, NumDigitsAfterDecimal)
End Function

Image by SplitShire from Pixabay