This tutorial explains how to create a custom Excel Access MRound Function to simulate Excel spreadsheet MRound Function round to nearest multiple.
You may also want to read:
VBA Excel Access roundup rounddown Function
Excel MRound Function
Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest $100, because we want the bonus amount looks better. For example, $11040 is rounded to $11000.
Access MRound Function
As of Access 2013, there is no Access MRound Function available. Even worse, Access Round Function uses round to even logic which is different from Excel Round Function. Therefore there is a need to create a custom Access MRound Function.
VBA Code – Excel Access MRound custom Function
Function wMRound(pValue As Double, multiple) As Double Dim negNumber As Boolean If pValue < 0 Then pValue = 0 - pValue negNumber = True End If If multiple < 0 Then multiple = 0 - multiple End If Select Case pValue / multiple - Int(pValue / multiple) Case Is < 0.5 result = Int(pValue / multiple) * multiple Case Is >= 0.5 result = Int(pValue / multiple) * (multiple) + multiple End Select If negNumber = True Then wMRound = 0 - result Else wMRound = result End If End Function
Algorithm – Excel Access MRound custom Function
This custom MRound Function basically looks at the decimal part of remainder after dividing pValue by multiple with the help of INT Function. If the remainder is >=0.5, we need to round down the pValue to maximum multiple less than pValue, and then add a multiple to the number.
Unlike Excel MRound Function, this custom MRound Function allow parameters to input negative pValue but positive Multiple (which I think make more sense than using negative Multiple)
Syntax – Excel Access MRound custom Function
wMRound(pValue, multiple)
| pValue | The value to round |
| Multiple | Multiple to use in round |
Although this Function can be used for Access and Excel, it is considered as mainly used for Access, because you can simply call the followings in Excel VBA
Application.WorksheetFunction.MRound
Example – Excel Access MRound custom Function
| Formula | Result | Explanation |
| =wmround(-1.1,1) | -1 | Allow negative number but positive multiple |
| =wmround(-1.2,-1) | -1 | Allow negative multiple, which will be regarded as positive |
| =wmround(-1.3,0.5) | -1.5 | Allow decimal multiple |
| =wmround(-1.5,0.5) | -1.5 | Return same value if remainder is 0 |
Outbound References
https://support.office.com/en-us/article/MROUND-function-C299C3B0-15A5-426D-AA4B-D2D5B3BAF427
