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:
=MROUND(A2, 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 down to the nearest 10.
Unfortunately this isn’t possible with the MROUND function but you can use the FLOOR function instead to accomplish this.
For example, you can use the following formula to round the value in cell A2 down to the nearest 10:
=FLOOR(A2, 10)
Whether the value in cell A2 is 23 or 28, it would get rounded to 20 each time.
The following example shows how to use the FLOOR function in practice.
Example: Round Down 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 down to the nearest 10.
The following screenshot shows the difference between using the MROUND and FLOOR 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 28 up to 30.
(2) The FLOOR function always rounds each value in column A down to the nearest multiple of 10.
For example, the FLOOR function rounds 28 down to 20.
Note: You can find the complete documentation for the FLOOR function in Excel here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Use MROUND to Always Round Up in Excel
How to Round to Significant Figures in Excel
How to Generate Random Number Between Range in Excel
