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

Manager@IBM
Wyman is human resources professional specialized in implementation of HR information system.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist