To round a number to the nearest 5, use =MROUND(A2,5). The second argument is the multiple you want, so =MROUND(A2,50) rounds to the nearest 50 and =MROUND(A2,0.5) to the nearest half. MROUND goes to whichever multiple is closer and rounds exact halves away from zero, so 12.5 becomes 15 with a multiple of 5.
To force the direction, use =FLOOR(A2,5) to always round down and =CEILING(A2,5) to always round up — useful for pricing, where you want everything pushed to the next 5 regardless.
Two things to watch: MROUND returns #NUM! if the number and the multiple have opposite signs, so use =MROUND(A2,-5) for negative values, and for negatives with FLOOR and CEILING, use FLOOR.MATH and CEILING.MATH instead, which handle the sign more predictably.
In this formula tutorial, we will learn to round a number to the nearest 5, 50, and .5. You can use these formulas in Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Excel for Mac.
Round to Nearest .5, 5, and 50
To round to the nearest 5, you can use the MROUND function. You can specify the argument to get the multiple of in this function. Look at the following formula.
=MROUND(A2,5)
In the above formula, you have used 5 as a multiple to round the value. Now, if you look at the first value in cell A2, which is 4, the nearest multiple of 5 is 5. And for the second value, you got 15, which is the nearest multiple of 5.
In the same way, with the MROUND function, you can use 50.
And the same function goes for .5.
Round Down to the Nearest 5
To round down a number to 5, you need to use the function FLOOR. This function has the same arguments that you have in the MROUND function.
=FLOOR(D8,5)
And in the same way for the 50.
And for .5 as well.
Round-Up to the Nearest 5
You must use the CEILING function to round up a number to the nearest 5.
For 50.
And in the end, for .5.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.