Rounding a number to the nearest 10, 100, or thousand is easy in Excel and useful in many cases. For example, you might need to simplify large numbers for reporting or make data easier to understand.
Why Use Nearest Rounding?
- Simplify Reports: Round numbers in reports to make them clearer and easier to read.
- Financial Calculations: Round to avoid tiny errors that can change financial results.
- Estimate Values: Quickly estimate numbers for an overview instead of precise values.
Tricks the ROUND function
The ROUND function is useful for rounding numbers in Excel. This function has 2 arguments:
- Number: The value you want to round.
- Number of Digits: Specifies how many decimal places to round to.
But there is a trick with the second argument of the functions. If you fill a negative value, -1, -2, -3, you round your number to the nearest 10, 100, or 1000
- Nearest 10:
=ROUND(number, -1) - Nearest 100:
=ROUND(number, -2) - Nearest 1000:
=ROUND(number, -3)
In the following example, we have replaced the function’s second argument with the cells’ values in row 2. This is why we have added a dollar in the reference. This is called a mixed reference.
Using this technique, you can easily round numbers to the nearest value you need. Remember to apply it when your goal is to simplify data presentation.
The function MROUND
An alternative method for rounding to the nearest ten, five, … involves utilizing the MROUND function. The “M” in MROUND stands for Multiple, allowing you to specify the desired multiple to round to. For instance, if you aim to round to the nearest tenth, you would input 10 as the second argument.
=MROUND(1234,10) =>1230
Alternatively, round to the nearest five using 5 for the second argument. This is the technique to use for grouping ages to build a population pyramid 💡
=MROUND(1234,5) =>1235
