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
For rounding to the nearest 5 you can use the MROUND function, in which you can specify the argument to get the multiple of. Look at the following formula.
=MROUND(A2,5)
In the above formula, you have used the 5 as a multiple to round the value. Now if you look at the first value in cell A2 that is 4 and 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
To round up a number to the nearest 5, you need to use the CEILING function.
For 50.
And in the end, for .5.
More Formulas
- Calculate Compound Interest in Excel
- Calculate Cube Root in Excel
- Calculate Percentage Variance (Difference)
- Calculate Simple Interest in Excel
- Calculate SQUARE ROOT in Excel
- Calculate the Weighted Average in Excel
- Does Not Equal Operator in Excel
- Round a Number to Nearest 1000, 100, and 10
- Square a Number in Excel
- Round a Number to Nearest 1000 in Excel
- Add-Subtract Percentage from a Number
- Average But Ignore Errors
- Average Number but Exclude Zeros
- Average Only Non-Blank Cells
- Calculate the Average of the Percentage Values
- Calculate the Average of the Time Values
- Calculate the Cumulative Sum of Values
- Round Percentage Values