You can use the MROUND function in Excel to round values to a particular multiple.
For example, you can use the following formula to round the value in cell A2 to the nearest 10:
If the value in cell A2 is 23 then the MROUND function will round the value to 20 since this is the nearest multiple of 10.
Or if the value in cell A2 is 28 then MROUND will round the value to 30 since this is the nearest multiple of 10.
However, suppose you would like to use the MROUND function to always round up to the nearest 10.
Unfortunately this isn’t possible with the MROUND function but you can use the CEILING function instead to accomplish this.
For example, you can use the following formula to round the value in cell A2 up to the nearest 10:
Whether the value in cell A2 is 23 or 28, it would get rounded to 30 each time.
The following example shows how to use the CEILING function in practice.
Example: Round Up to Nearest Multiple in Excel
Suppose we have the following list of values in Excel:
Suppose we would like to round each value in the list up to the nearest 10.
The following screenshot shows the difference between using the MROUND and CEILING functions in Excel to achieve this:
Notice the difference between the two functions:
(1) The MROUND function rounds each value in column A to the nearest multiple of 10. Sometimes this requires rounding the values up and other times it requires rounding the values down.
For example, the MROUND function rounds 23 down to 20.
(2) The CEILING function always rounds each value in column A up to the nearest multiple of 10.
For example, the CEILING function rounds 23 up to 30.
Note: You can find the complete documentation for the CEILING function in Excel here.
The following tutorials explain how to perform other common tasks in Excel: