Rounding Function MRound of Excel

Introduction.

Normally, numbers are rounded in calculations when the fractional part is 0.5 or more, moving up to the next whole number, or truncated otherwise. However, there are situations where the fractional part itself needs to be rounded to a specific precision level — such as during monetary disbursements like wages.

For example, in currency handling, if a value ends in 15 cents or 30 cents, it may need to be rounded to 25 cents and 50 cents, respectively, to ensure the correct distribution of coin denominations. Suppose you have only 50-cent coins available — in that case, any amount of 25 cents or more should be rounded up to 50 cents, and amounts below 25 cents should be rounded down to zero.

To achieve this, we need a function that accepts both a numeric value and a precision level, calculates the nearest multiple of that precision, and rounds up when the remainder equals or exceeds half of the precision value. This precision value doesn’t have to be fractional; it can be any numeric increment.

In MS Access, the built-in Round() function only rounds numbers to a specified number of decimal places or to the nearest integer, following standard rounding rules. MS Excel, however, provides a Worksheet Function MRound() that can perform such precision-based rounding. Unfortunately, no equivalent function exists in MS Access — and we can’t rely on Excel when this functionality is needed directly within Access.

The MRound Function

We will write an MRound() Function in Access with the same name.

So here it is:

Public Function MRound(ByVal Number As Double, ByVal Precision As Double) As Double
Dim Y As Double

On Error GoTo MRound_Err

   Y = Int(Number / Precision) * Precision
   MRound = IIf(((Number - Y) / Precision * 100 + 0.1) >= 50, Y + Precision, Y)

MRound_Exit:
Exit Function

MRound_Err:
MsgBox Err.Description, , "MRound()"
MRound = 0
Resume MRound_Exit
End Function

Save the Function and do Test Runs.

Copy and paste the above Code into a Global Module of your Database and save the Module.

Open the Debug Window (Ctrl+G) to try it out directly.

Sample Runs:

? Mround(123.3,0.2)

Result: 123.4

? Mround(123.24,0.5)

Result: 123

? Mround(123.25,0.5)

Result: 123.5

? Mround(123.74,0.5)

Result: 123.5

? Mround(123.75,0.5)

Result: 124

? Mround(10,3)

Result: 9

? Mround(11,3)

Result: 12

Add to your Function Library

If you would like to share this Function across your Other MS-Access Databases, then create a Library Database with the Function in it and set a Reference to the Library Database through Tools -> References in the VBA Window.

Earlier Post Link References:

1. Roundup Excel Function in MS Access
2. Proper Excel Function in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007
7. Microsoft Excel-Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel- 2
11. Opening an Excel Database Directly
12. Create Excel, Word Files from Access