Excel Access MRound custom Function round to nearest multiple

This tutorial explains how to create a custom Excel Access MRound Function to simulate Excel spreadsheet MRound Function round to nearest multiple

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. 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

 

Excel Access MRound custom Function round to nearest multiple