Rounding values up, down, by 4/5, or to significant figures

Gustav BrockMVP
CERTIFIED EXPERT
Published:
Updated:
Edited by: Andrew Leniart
This collection of functions covers all the normal rounding methods of just about any numeric value - at extreme precision.

Rounding should be right at hand


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, while the last exists in three varieties - for Currency, Decimal, and Double respectively.

They all accept a specified count of decimals - including a negative count which will round to tens, hundreds, etc. Those with Variant as return type will return Null for incomprehensible input.

 

Background


More than ten years 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 Int(Value + 0.5). They can all be found here.


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


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


rounding method

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

-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 examples can be found in the two modules in the code with suffix Test.

 

The functions


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

 

' Common constants. 
' 
' Base values. 
Public Const Base2      As Double = 2 
Public Const Base10     As Double = 10 


' Rounds Value by 4/5 with count of decimals as specified with parameter NumDigitsAfterDecimal. 
' 
' Rounds to integer if NumDigitsAfterDecimal 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() to prevent bit errors of reals. 
' 
' Execution time is about 1µs. 
' 
' 2018-02-09. Gustav Brock, Cactus Data ApS, CPH. 
' 
Public Function RoundMid( _ 
    ByVal Value As Variant, _ 
    Optional ByVal NumDigitsAfterDecimal 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 ^ NumDigitsAfterDecimal) 
         
        If Scaling = 0 Then 
            ' A very large value for NumDigitsAfterDecimal 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 NumDigitsAfterDecimal 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 NumDigitsAfterDecimal 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

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:


RoundedValue = RoundMid(32.675, 2)

However, it performs a normal 4/5 by default, 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 is somewhat different, though scaling and rounding still is an essential part:

' 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. 
' 
' 2018-02-09. Gustav Brock, Cactus Data ApS, CPH. 
' 
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. 
' 
' 2018-02-09. Gustav Brock, Cactus Data ApS, CPH. 
' 
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


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

 



More options


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.

Also, a lot of variations is possible using the functions as a base.

For example, given the value n = 128.19:


Round to the nearest quarter (0.25):

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

Round up to the nearest "bargain price"


RoundedValue = RoundUp(n) - 0.01
RoundedValue -> 128.99

Round to the nearest integer 5:


RoundedValue = RoundMid(n / 5) * 5
RoundedValue = 130.00


Downloads


The current version can always be found at GitHub.

The version 1.3.2 demo files for Office 365 is here: 

Rounding 1.3.2.accdb

Rounding 1.3.2.xlsm


Further reading


My other articles on rounding:

Round elements of a sum to match a total
Round by the power of two

I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.


Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.


Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.



3
3,944 Views
Gustav BrockMVP
CERTIFIED EXPERT

Comments (2)

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Author

Commented:
Finally I have these assembled. Through the years bits and pieces were collected, but now I had the option for a rewrite.

/gustav

PS: There are minor flaws in the layout but the editor is somewhat strange so I couldn't get it completely straight.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Nicely laid out.  Voted Yes.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community