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.

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


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



Leave a Reply

Your email address will not be published.