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