This tutorial explains how to use Access Round Function (round to even logic) and create a custom Round Function to simulate Excel Round Function.
You may also want to read:
VBA Excel Access roundup rounddown Function
Excel Access MRound custom Function round to nearest multiple
Access Round Function – Round to even logic
Access Round Function is completely different from Excel Round Function.
For Excel Round Function, if decimal part is >=0.5, the integer rounds up, otherwise the integer rounds down.
For Access Round Function, as well as Access VBA, Excel VBA, the Round Function uses “Round to even” logic. Integer rounds up if decimal part >=0.6, round down if <=0.4. For case decimal part exactly =0.5, , then round down, otherwise round up(round up to even integer), same logic applies to negative number (See example).
I summarize the Access round to even logic below.
If decimal part >=0.6 Then Round up ElseIf decimal part <=0.4 Then Round down ElseIf decimal part = 0.5 Then If integer part is even number Then Round down ElseIf integer part is odd number Then Round up End If End If
Round to even logic has other names:
-unbiased rounding
-convergent rounding
-statistician’s rounding
-Dutch rounding
-Gaussian rounding
-odd-even rounding
-bankers’ rounding
-broken rounding
Syntax of Access Round Function – Round to even logic
Round ( expression, [ decimal_places ] )
If [decimal_places] is omitted, default is 0 (round to integer)
Example of Access Round Function – Round to even logic
Round([Number],0)
| Number | Access Round | Excel Round | Check Diff |
| 0.1 | 0 | 0 | |
| 0.2 | 0 | 0 | |
| 0.3 | 0 | 0 | |
| 0.4 | 0 | 0 | |
| 0.5 | 0 | 1 | integer part is even number, round down |
| 0.6 | 1 | 1 | |
| 0.7 | 1 | 1 | |
| 0.8 | 1 | 1 | |
| 0.9 | 1 | 1 | |
| 1 | 1 | 1 | |
| 1.1 | 1 | 1 | |
| 1.2 | 1 | 1 | |
| 1.3 | 1 | 1 | |
| 1.4 | 1 | 1 | |
| 1.5 | 2 | 2 | integer part is odd number, round up |
| 1.6 | 2 | 2 | |
| 1.7 | 2 | 2 | |
| 1.8 | 2 | 2 | |
| 1.9 | 2 | 2 | |
| 2 | 2 | 2 | |
| 2.1 | 2 | 2 | |
| 2.2 | 2 | 2 | |
| 2.3 | 2 | 2 | |
| 2.4 | 2 | 2 | |
| 2.5 | 2 | 3 | integer part is even number, round down |
| 2.6 | 3 | 3 | |
| 2.7 | 3 | 3 | |
| 2.8 | 3 | 3 | |
| 2.9 | 3 | 3 | |
| 3 | 3 | 3 | |
| 3.1 | 3 | 3 | |
| 3.2 | 3 | 3 | |
| 3.3 | 3 | 3 | |
| 3.4 | 3 | 3 | |
| 3.5 | 4 | 4 | integer part is odd number, round up |
| 3.6 | 4 | 4 | |
| 3.7 | 4 | 4 | |
| 3.8 | 4 | 4 | |
| 3.9 | 4 | 4 | |
| 4 | 4 | 4 | |
| 4.1 | 4 | 4 | |
| 4.2 | 4 | 4 | |
| 4.3 | 4 | 4 | |
| 4.4 | 4 | 4 | |
| -4.5 | 4 | 5 | integer part is even number, round down |
| -4.6 | 5 | 5 | |
| -4.7 | 5 | 5 | |
| -4.8 | 5 | 5 | |
| -4.9 | 5 | 5 | |
| -5 | 5 | 5 | |
| -5.1 | 5 | 5 | |
| -5.2 | 5 | 5 | |
| -5.3 | 5 | 5 | |
| -5.4 | 5 | 5 | |
| -5.5 | 6 | 6 | integer part is odd number, round up |
VBA Code of custom Access Round Function – simulate Excel Round function
Some custom Round Function you can find on the Internet do not consider cases for negative number , but this one does.
Function wRound(pValue As Double, digit) As Double Dim ExpandedValue Dim IntPart As Long Dim FractionPart As Double Dim negNumber As Boolean If pValue < 0 Then pValue = 0 - pValue negNumber = True End If ExpandedValue = pValue * (10 ^ digit) 'Retrieve integer part of the number IntPart = Int(ExpandedValue) FractionPart = ExpandedValue - IntPart 'Retrieve the fraction part of the number If FractionPart < 0.5 Then result = IntPart / 10 ^ digit ElseIf FractionPart >= 0.5 Then result = (IntPart + 1) / 10 ^ digit End If If negNumber = True Then wRound = 0 - result Else wRound = result End If End Function
Syntax of custom Access Round Function – simulate Excel Round function
wRound(pValue, digit)
| pValue | the decimal value you want to convert |
| digit | the number of decimal places you want to convert to |
Outbound References
http://www.techonthenet.com/access/functions/numeric/round.php