top of page

How to use the MROUND Function in Excel - 3 Examples


How to use the MROUND Function in Excel - 3 Examples

The MROUND function will round any number up or down to the nearest given multiple.

Contents:


Syntax

= MROUND(number, multiple)

number = the number you wish to round

multiple = the given multiple which will be rounded to


Explanation

The MROUND function is part of the "Math and Trigonometry" group of functions within Excel.


This function will round any number, positive or negative, to a desired multiple. A multiple being any number than can be divided by another number without a remainder left over.


For example, 7 rounded to the nearest multiple of 10, would be rounded upwards to 10. While a 7 rounded to the nearest multiple of 5, would be rounded downwards to 5.


If the number is already an exact multiple, no rounding will be performed.


Notes:

- If a number is directly in-between two multiples (3 between multiples of 2, 2 and 4), then the formula will favor rounding upwards to the higher multiple

- If the either input is non-numerical, a #VALUE! error will be returned

- The number and the multiple must have the same sign

- A blank cell will be returned as a 0


Different Methods of Rounding in Excel

There are a lot of different ways to round numbers, and it all depends on the end goal you're trying to reach. Here are a few different rounding functions and when you would want to use them:


ROUND - normal rounding, rounds a specified place

MROUND - rounds to the nearest specified multiple

ROUNDUP - rounds up away from 0, to the nearest specified place

CEILING.MATH - rounds up to the nearest integer or significant multiple

ROUNDDOWN -rounds down away from 0, to the nearest specified place

FLOOR.MATH - rounds down to the nearest integer or significant multiple

INT - rounds down to the nearest integer, returns an integer

TRUNC - truncates all decimal places to specified place


Examples

1. How to Round a Number to The Nearest Multiple

To round a number to the nearest multiple, we can use the MROUND function with any number (either a cell reference or hard coded value) and combine that with a multiple value.


In each row, the input numbers are being rounded to the nearest multiple as set by the values in column C.


To round to negative multiples, both the input and multiple must be negative.

= MROUND(number, multiple)
How to Round a Number to The Nearest Multiple using MROUND


2. How to Round a Number to the Nearest Ones, Tens, Hundreds, Thousands Place, or Greater Using MROUND

The MROUND function can also be used to round numbers to the nearest thousand, ten thousand, million, or any higher threshold.


With this method, any number can be round to any degree of precision needed.


Multiple Inputs and Rounding Results

1 - round to the nearest ones

10 - round to the nearest tens

100 - round to the nearest hundred

1000 - round to the nearest thousand

10000 - round to the nearest ten thousand

100000 - round to the nearest hundred thousand

1000000 - round to the nearest million

= MROUND(number, multiple)
How to Round a Number to the Nearest Ones, Tens, Hundreds, Thousands Place, or Greater Using MROUND


3. How to Round a Time to the Nearest Hour, Half Hour, or Quarter Hour Using MROUND

The MROUND function can also be used to round any given time to the nearest quarter hour, half hour, or full hour.


These formulas work by taking any an input time and rounding to the nearest multiple of 15 minutes, half an hour, or full hour.


Formula to round to the nearest quarter hour:

= MROUND(time, "0:15")

Formula to round to the nearest half hour:

= MROUND(time, "0:30")

Formula to round to the nearest full hour:

= MROUND(time, "1:00")
How to Round a Time to the Nearest Hour, Half Hour, or Quarter Hour Using MROUND


bottom of page