In this step-by-step guide, we’ll show you how to round numbers to the nearest multiple of five using two different methods in Microsoft Excel.
Why round numbers to the nearest 5?
Rounding numbers to the nearest five is a common task in finance, accounting, and data analysis. It helps simplify complex calculations and makes it easier to understand large datasets. By rounding numbers to the nearest five, you can quickly identify trends and patterns that may have gone unnoticed otherwise.
Method 1: Using the MROUND function
If you’re using Excel 2007 or later, you can use the MROUND function to round a number to the nearest multiple of five. This function is similar to the ROUND function but offers more flexibility. Here’s how:
Step 1: Find the cell containing the number you want to round. Find and select the cell containing the number you want to round. For example, let’s say we want to round the number 23.7 in cell A2.
Step 2: Type =MROUND(A2, 5) and press enter. Type =MROUND(A1, 5) in the formula bar, where A2 is the cell containing the number you want to round. The 5 argument tells Excel to round the number to the nearest multiple of five.
Step 3: Review the result. The result should be 25, which is the number rounded to the nearest multiple of five.
Live example of both methods
The easiest way to get the hang of this might be to try it for yourself. You can try out both methods in the interactive editor below.
Method 2: Using the ROUND function (for earlier Excel versions)
The ROUND function alone does not directly round to the nearest multiple of a specific number, like 5, because it is designed to round to a specified number of decimal places. However, you can still use it creatively with some math to get the same result.
To round a number to the nearest multiple of five using the ROUND function, follow these steps:
Step 1: Find the cell containing the number you want to round. Select the cell containing the number you want to round. For example, if the number is 7 and is located in cell A2.
Step 2: Type =ROUND(A2 / 5, 0) * 5 and press enter. Type =ROUND(A2 / 5, 0) * 5 in the formula bar, where A2 is the cell containing the number you want to round. The ROUND function rounds the number to the nearest multiple of five by dividing the number by 5, rounding it, and then multiplying it back by 5.
Step 3: Review your result. The result will be 5, which is the number rounded to the nearest multiple of five.
For more info about rounding in Excel, see our complete rounding guide in Excel.