Click here to Skip to main content
15,867,330 members
Articles / VBA
Tip/Trick

Rounding Values Up, Down, By 4/5, Or To Significant Figures

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
9 Oct 2015CPOL5 min read 32.7K   216   6   14
Generic VBA functions for all the normal rounding methods - of any value that VBA can handle.

Introduction

In many areas, rounding that accurately follows specific rules are needed - accounting, statistics, insurance, etc.

Unfortunately, the native functions of VBA that can perform rounding are either missing, limited, inaccurate, or buggy, and all address only a single rounding method. The upside is that they are fast, and that may in some situations be important.

However, often precision is mandatory, and with the speed of computers today, a little slower processing will hardly be noticed, indeed not for processing of single values. All the functions presented here run at about 1 µs.

They cover the normal rounding methods:

  • Round down, with the option to round negative values towards zero
  • Round up, with the option to round negative values away from zero
  • Round by 4/5, either away from zero or to even (Banker's Rounding)
  • Round to a count of significant figures

The first three functions accept all the numeric data types. They all accept a specified count of decimals - including a negative count which will round to tens, hundreds, etc.

The last exists in three varieties - for Currency, Decimal, and Double respectively.

Those with Variant as return type will return Null for incomprehensible input.

Background

More than a decade ago, Donald Lessau created a site dealing with Visual Basic issues: VBspeed. One of these issues was to create a replacement for Round which was (and still is) buggy, though fast. Also, it could (and still can) only perform Banker's Rounding which may not be what you expect when you look for 4/5 rounding. Several suggestions were put forward, one extremely simple using Format; but string handling, as it is, is not very fast, so other solutions were thought out, all more or less wrapped around the classic rounding method Int(Value + 0.5). They can all be found here.

If you are convinced that Round is not buggy, just try this simple example:

VB.NET
RoundedValue = Round(32.675, 2)

It will return 32.67 while both a normal 4/5 rounding as well as Banker's Rounding would return 32.68.

Today, computers are much faster, and while Round is very fast, it will in many cases be preferable with a function that is a bit slower if it on the other hand always returns the expected result.

So - from these old contributions - I've brushed up the old 4/5 rounding function with an option for choosing Banker's Rounding, and added sibling functions for rounding up or down (also with options) with focus on the ability to correctly handle as wide a range of input values as possible. Still, they run at about 1 µs. Finally, for completeness and because it is quite different from the the other functions, a function for rounding to significant figures was added.

It's important to stress that there is no right or wrong rounding method, thus it makes no sense to argue why Mid Rounding away from zero is "better" than Banker's Rounding. What's important, however, is to know how each method operates and which side effects this may cause, so you can choose the optimum method for the current task.

Examples

It can be useful to list examples that shows the differences between the different rounding methods and how they act upon positive as well as negative values. Here are just a few.

VB.NET
Value n                                  12.344    12.345    12.346    12.354    12.355    12.356
RoundUp(n, 2, False)                     12.35     12.35     12.35     12.36     12.36     12.36
RoundUp(n, 2, True)                      12.35     12.35     12.35     12.36     12.36     12.36
RoundDown(n, 2, False)                   12.34     12.34     12.34     12.35     12.35     12.35
RoundDown(n, 2, True)                    12.34     12.34     12.34     12.35     12.35     12.35
RoundMid(n, 2, False)                    12.34     12.35     12.35     12.35     12.36     12.36
RoundMid(n, 2, True)                     12.34     12.34     12.35     12.35     12.36     12.36
RoundSignificantDec(n, 4, , False)       12.34     12.35     12.35     12.35     12.36     12.36
RoundSignificantDec(n, 4, , True)        12.34     12.34     12.35     12.35     12.36     12.36

Value n                                 -12.344   -12.345   -12.346   -12.354   -12.355   -12.356
RoundUp(n, 2, False)                    -12.34    -12.34    -12.34    -12.35    -12.35    -12.35
RoundUp(n, 2, True)                     -12.35    -12.35    -12.35    -12.36    -12.36    -12.36
RoundDown(n, 2, False)                  -12.35    -12.35    -12.35    -12.36    -12.36    -12.36
RoundDown(n, 2, True)                   -12.34    -12.34    -12.34    -12.35    -12.35    -12.35
RoundMid(n, 2, False)                   -12.34    -12.35    -12.35    -12.35    -12.36    -12.36
RoundMid(n, 2, True)                    -12.34    -12.34    -12.35    -12.35    -12.36    -12.36
RoundSignificantDec(n, 4, , False)      -12.34    -12.35    -12.35    -12.35    -12.36    -12.36
RoundSignificantDec(n, 4, , True)       -12.34    -12.34    -12.35    -12.35    -12.36    -12.36

More can be found in the two modules in the code with suffix Test.

Using the Code

Normal Rounding

The main function - rounding by 4/5 - goes like this. Please note the in-line comments for details:

VB.NET
' Common constants.
'
Public Const Base10     As Double = 10

' Rounds Value by 4/5 with count of decimals as specified with parameter NumDigitsAfterDecimals.
'
' Rounds to integer if NumDigitsAfterDecimals is zero.
'
' Rounds correctly Value until max/min value limited by a Scaling of 10
' raised to the power of (the number of decimals).
'
' Uses CDec() for correcting bit errors of reals.
'
' Execution time is about 1µs.
'
Public Function RoundMid( _
    ByVal Value As Variant, _
    Optional ByVal NumDigitsAfterDecimals As Long, _
    Optional ByVal MidwayRoundingToEven As Boolean) _
    As Variant

    Dim Scaling     As Variant
    Dim Half        As Variant
    Dim ScaledValue As Variant
    Dim ReturnValue As Variant
   
    ' Only round if Value is numeric and ReturnValue can be different from zero.
    If Not IsNumeric(Value) Then
        ' Nothing to do.
        ReturnValue = Null
    ElseIf Value = 0 Then
        ' Nothing to round.
        ' Return Value as is.
        ReturnValue = Value
    Else
        Scaling = CDec(Base10 ^ NumDigitsAfterDecimals)
       
        If Scaling = 0 Then
            ' A very large value for Digits has minimized scaling.
            ' Return Value as is.
            ReturnValue = Value
        ElseIf MidwayRoundingToEven Then
            ' Banker's rounding.
            If Scaling = 1 Then
                ReturnValue = Round(Value)
            Else
                ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
                ' Very large values for NumDigitsAfterDecimals can cause an out-of-range error 
                ' when dividing.
                On Error Resume Next
                ScaledValue = Round(CDec(Value) * Scaling)
                ReturnValue = ScaledValue / Scaling
                If Err.Number <> 0 Then
                    ' Decimal overflow.
                    ' Round Value without conversion to Decimal.
                    ReturnValue = Round(Value * Scaling) / Scaling
                End If
            End If
        Else
            ' Standard 4/5 rounding.
            ' Very large values for NumDigitsAfterDecimals can cause an out-of-range error 
            ' when dividing.
            On Error Resume Next
            Half = CDec(0.5)
            If Value > 0 Then
                ScaledValue = Int(CDec(Value) * Scaling + Half)
            Else
                ScaledValue = -Int(-CDec(Value) * Scaling + Half)
            End If
            ReturnValue = ScaledValue / Scaling
            If Err.Number <> 0 Then
                ' Decimal overflow.
                ' Round Value without conversion to Decimal.
                Half = CDbl(0.5)
                If Value > 0 Then
                    ScaledValue = Int(Value * Scaling + Half)
                Else
                    ScaledValue = -Int(-Value * Scaling + Half)
                End If
                ReturnValue = ScaledValue / Scaling
            End If
        End If
        If Err.Number <> 0 Then
            ' Rounding failed because values are near one of the boundaries of type Double.
            ' Return value as is.
            ReturnValue = Value
        End If
    End If
   
    RoundMid = ReturnValue

End Function

To perform the rounding, the value to be rounded is scaled up or down by a power of ten determined by the required number of decimals, converted to Decimal to avoid bit errors, and an integer rounding is done. If this fails due to the limited range of Decimal, it falls back using Double. Finally, the value is scaled back and returned.

Using it requires nothing more than importing (or copy/paste) the module RoundingMethods included in the zip into your project. Then the functions can be used in a similar way that you would use Round:

VBScript
RoundedValue = RoundMid(32.675, 2)

However, by default, it performs a normal 4/5 and optionally Banker's Rounding.

It is supplemented by the rounding up or down functions:

  • RoundUp
  • RoundDown

These act basically like -Int(-n) or Int(n) but also feature an option for rounding away from zero or towards zero respectively (see the example results above).

Rounding to Significant Figures

Rounding to significant figures is somewhat different, though scaling and rounding still is an essential part:

VB.NET
' Rounds Value to have significant figures as specified with parameter Digits.
'
' Performs no rounding if Digits is zero.
' Rounds to integer if NoDecimals is True.
' Digits can be any value between 1 and 14.
'
' Will accept values until about max/min Value of Double type.
' At extreme values (beyond approx. E+/-300) with significant
' figures of 10 and above, rounding is not 100% perfect due to
' the limited precision of Double.
'
' For rounding of values within the range of type Decimal, use the
' function RoundSignificantDec.
'
' Requires:
'   Function Log10.
'
Public Function RoundSignificantDbl( _
    ByVal Value As Double, _
    ByVal Digits As Integer, _
    Optional ByVal NoDecimals As Boolean, _
    Optional ByVal MidwayRoundingToEven As Boolean) _
    As Double
   
    Dim Exponent    As Double
    Dim Scaling     As Double
    Dim Half        As Variant
    Dim ScaledValue As Variant
    Dim ReturnValue As Double
   
    ' Only round if result can be different from zero.
    If (Value = 0 Or Digits <= 0) Then
        ' Nothing to round.
        ' Return Value as is.
        ReturnValue = Value
    Else
        ' Calculate scaling factor.
        Exponent = Int(Log10(Abs(Value))) + 1 - Digits
        If NoDecimals = True Then
            ' No decimals.
            If Exponent < 0 Then
                Exponent = 0
            End If
        End If
        Scaling = Base10 ^ Exponent
       
        If Scaling = 0 Then
            ' A very large value for Digits has minimized scaling.
            ' Return Value as is.
            ReturnValue = Value
        Else
            ' Very large values for Digits can cause an out-of-range error when dividing.
            On Error Resume Next
            ScaledValue = CDec(Value / Scaling)
            If Err.Number <> 0 Then
                ' Return value as is.
                ReturnValue = Value
            Else
                ' Perform rounding.
                If MidwayRoundingToEven = False Then
                    ' Round away from zero.
                    Half = CDec(Sgn(Value) / 2)
                    ReturnValue = CDbl(Fix(ScaledValue + Half)) * Scaling
                Else
                    ' Round to even.
                    ReturnValue = CDbl(Round(ScaledValue)) * Scaling
                End If
                If Err.Number <> 0 Then
                    ' Rounding failed because values are near one of the boundaries of type Double.
                    ' Return value as is.
                    ReturnValue = Value
                End If
            End If
        End If
    End If
 
    RoundSignificantDbl = ReturnValue

End Function

' Returns Log 10 of Value.
'
Public Function Log10( _
    ByVal Value As Double) _
    As Double

    ' No error handling as this should be handled
    ' outside this function.
    '
    ' Example:
    '
    '     If MyValue > 0 then
    '         LogMyValue = Log10(MyValue)
    '     Else
    '         ' Do something else ...
    '     End If
   
    Log10 = Log(Value) / Log(Base10)

End Function

To perform the rounding, the value to be rounded is scaled up or down by a power of ten determined by the logarithm10 of the value, converted to Decimal to avoid bit errors, and an integer rounding is done. If this fails due to the limited range of Decimal, it falls back using Double. Finally, the value is scaled back and returned.

This is an example with one value scaled and rounded to four significant figures:

Input value: 0.030675    - Rounded value: 0.03068
Input value: 0.30675     - Rounded value: 0.3068
Input value: 3.0675      - Rounded value: 3.068
Input value: 30.675      - Rounded value: 30.68
Input value: 306.75      - Rounded value: 306.8
Input value: 3067.5      - Rounded value: 3068
Input value: 30675       - Rounded value: 30680
Input value: 306750      - Rounded value: 306800
Input value: 3067500     - Rounded value: 3068000
Input value: 30675000    - Rounded value: 30680000

For all functions, note that potential floating point errors are avoided by casting to Decimal with CDec.

The current version can always be found at GitHub.

The attached zip contains code for all the functions, a test module, as well as a Microsoft Access 2013 project.

Variations

A lot of variations are possible using the functions as a base.
For example, given the value n = 128.19:

Round to the nearest quarter (0.25):

VB.NET
RoundedValued = RoundMid(n / 0.25) * 0.25
RoundedValued -> 128.25

Round to the nearest integer 5:

VB.NET
RoundedValue = RoundMid(n / 5) * 5
RoundedValue = 130.00

Round up to the nearest bargain price:

VB.NET
RoundedValue = RoundUp(n) - 0.01
RoundedValue -> 128.99

Points of Interest

If you wish to study the peculiars of the native Round, then study the module RoundingMethodsTest where a lot of values and results can be found. Also, should you wish to modify a function for your specific purpose, as a minimum it should pass the test included in the test module.

History

  • Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Cactus Data ApS
Denmark Denmark
Microsoft MVP, Most Valuable Professional, since 2017.

It started with BASIC in the 80s, then moved to PDC Prolog, and then - when Access 1.0 was launched - "back to Basic", or rather Access Basic. Then followed VBA (Visual Basic for Applications) starting with Microsoft Access 95/97 and still going with Access 2019/365.

Still I do some VBA, but since 2008 my main focus has been C#.

Application area is mainly accounting and custom applications related to accounting.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Farhad Reza15-Oct-15 21:02
Farhad Reza15-Oct-15 21:02 
GeneralRe: My vote of 5 Pin
Gustav Brock15-Oct-15 22:45
professionalGustav Brock15-Oct-15 22:45 
SuggestionA more compact VB.Net version that can round in any direction... Pin
Graeme_Grant9-Oct-15 16:04
mvaGraeme_Grant9-Oct-15 16:04 
AnswerRe: A more compact VB.Net version that can round in any direction... Pin
Gustav Brock10-Oct-15 7:29
professionalGustav Brock10-Oct-15 7:29 
GeneralRe: A more compact VB.Net version that can round in any direction... Pin
Graeme_Grant18-Oct-15 2:03
mvaGraeme_Grant18-Oct-15 2:03 
AnswerRe: A more compact VB.Net version that can round in any direction... Pin
Gustav Brock18-Oct-15 7:20
professionalGustav Brock18-Oct-15 7:20 
GeneralRe: A more compact VB.Net version that can round in any direction... Pin
Graeme_Grant19-Oct-15 22:06
mvaGraeme_Grant19-Oct-15 22:06 
AnswerRe: A more compact VB.Net version that can round in any direction... Pin
Gustav Brock19-Oct-15 23:49
professionalGustav Brock19-Oct-15 23:49 
GeneralRe: A more compact VB.Net version that can round in any direction... Pin
Graeme_Grant20-Oct-15 13:26
mvaGraeme_Grant20-Oct-15 13:26 
AnswerRe: A more compact VB.Net version that can round in any direction... Pin
Gustav Brock20-Oct-15 21:37
professionalGustav Brock20-Oct-15 21:37 
QuestionSo your functions? What makes them different from System.Math.Round? Pin
Jazzynupe9-Oct-15 10:51
Jazzynupe9-Oct-15 10:51 
AnswerRe: So your functions? What makes them different from System.Math.Round? Pin
Gustav Brock10-Oct-15 7:18
professionalGustav Brock10-Oct-15 7:18 
PraiseRe: So your functions? What makes them different from System.Math.Round? Pin
Jazzynupe6-Jan-16 11:19
Jazzynupe6-Jan-16 11:19 
AnswerRe: So your functions? What makes them different from System.Math.Round? Pin
Gustav Brock6-Jan-16 21:28
professionalGustav Brock6-Jan-16 21:28 
No problem, and thanks for the clarification.
Happy New Year!

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.