How to Round Excel Values to the Nearest 10 or 100
Question details
The user needs to round a range of numerical values to specific multiples, such as the nearest 10 or 100, or consistently round numbers strictly up or down.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing financial data, inventory counts, or other numerical values that require rounding to clean multiples like 10 or 100.
- Observed behavior
- Numbers are displayed in exact, unrounded values instead of being conformed to the desired nearest multiple.
Ensure your dataset is formatted as numbers or currency, and determine whether you need standard rounding to the nearest multiple, or if you need to strictly round up or down.
Use the MROUND Function to Round to the Nearest Multiple
The MROUND function is the most direct way to round a number to a specific multiple, such as 10 or 100.
MROUND calculates the nearest multiple based on standard rounding rules. For example, when rounding to the nearest 10, values ending in 1 through 4 are rounded down, while values ending in 5 through 9 are rounded up.
Click on an empty cell adjacent to your data (for example, cell B2) where you want the rounded result to appear.
Type =MROUND(A2, 10) to round the value in cell A2 to the nearest 10. If you want to round to the nearest 100, change the formula to =MROUND(A2, 100).
Press Enter to view the result. Click on the completed cell and drag the fill handle (the small square at the bottom-right corner) down to apply the formula to the rest of your data column.
Use FLOOR or CEILING for Directional Rounding
If you need numbers to consistently round strictly down or strictly up regardless of the closest multiple, use the FLOOR or CEILING functions.
Use a VBA Macro to Round Cells in Bulk
Ideal for advanced users, this method uses a VBA script to replace original values directly with rounded values, skipping the need for an extra formula column.
Round Numbers Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports MROUND, FLOOR, and CEILING functions, allowing you to instantly round financial data or numerical values exactly how you want. Enjoy a smooth, familiar interface equipped with all the analytical tools you need.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the numerical values that require rounding.
- 2. Apply the rounding formula: Select an adjacent empty cell and type =MROUND(A2, 10) to round to the nearest ten, or adjust the multiple to 100 as needed.
- 3. Autofill your column: Press Enter, then double-click the green fill square at the bottom right corner of the cell to instantly apply the rounding formula to all your data rows.
Frequently Asked Questions
Why does the MROUND function return a #NUM! error?
This error occurs if the original number and the multiple you are rounding to have different signs. For example, if your number is positive and the multiple is negative, MROUND will fail. Ensure both values are either positive or negative.
How do I round a number to the nearest thousand?
You can use the exact same functions but increase the multiple parameter. For example, using =MROUND(A2, 1000) will round the value to the nearest 1,000.
Can I round numbers just by using the 'Decrease Decimal' button?
No. The 'Decrease Decimal' button only changes how the number is visually displayed on your screen. The actual underlying value stored in the cell remains exact. To permanently alter the mathematical value to a specific multiple like 10 or 100, you must use a formula like MROUND or a VBA macro.
