The MROUND function in excel is used to round a number to it’s nearest specified multiple.
- Number – This is the number to be rounded
- Multiple – This is the nearest multiple to which the number to be rounded
We will understand the behavior of MROUND function in detail with a simple example as follows.
In cell A1 we have added the below formula.
As you can see below the number 99 has been rounded to 100 which is the nearest multiple of 5.
The MROUND function can be used to round any kind of numbers to the nearest multiple of integers or decimal numbers as well. The only criteria is both the number and multiple should be of same sign.
As you can see below we have some numbers in column A which we will round to the nearest multiple of the respective cells in column B. You can see here we have rounded integer to the nearest multiple of a decimal and also the decimal is rounded to the nearest multiple of decimal and the decimal is rounded to the nearest multiple of integer and so on.
In the above example we can also see in 4th row the number in cell A4 is already an exact multiple of cell B4 hence in cell C4 no rounding happened.
As you can see below the MROUND function in cell C1 & C2 resulted errors as the number & multiple mentioned in cell A1 & B1 and A2 & B2 are of different signs. But in 3rd row the number in A3 and the multiple in B3 both are negative, which is of the same sign, hence MROUND resulted no error in cell C3.
In our last example we will learn how we can use the MROUND function to round a time. In the below example, in column B, we have rounded the times mentioned in cell A1 and A2 to the nearest multiple of 30 minutes.
Below is the formula we used in cell B1 and dragged the same for cell B2 to update the cell reference to A2.
With this formula, the time 7:29AM has been rounded to 7:30 AM which is the nearest multiple of 30 minutes. Same way in the next row, the time 11:55PM has been rounded to 12:00 AM.