Md. Abdul Kader
In certain circumstances, we prefer the rounded or approximate number rather than the exact number to make communication easier. For example, the exact population of New York is 8,253,213. But we like to say it is around 8 million. So, you may need your given number to round to the nearest 100 in Excel.
In this article, you’ll find the six quickest ways to round any given number to the nearest hundred (100) in Excel.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
6 Methods to Round to Nearest 100 in Excel
As shown in the following figure, we have the unit price for each Product ID.
Now, we have to round the unit prices to the nearest 100.
How can we do that?
It’s really a simple task.
Let’s get started.
Here, we have used the Microsoft 365 version. You can use any other version at your convenience.
1. Using ROUND Function
Firstly, we may utilize the ROUND function, one of the most popular functions used for rounding any number. The function returns a number rounded to a number of digits. Follow the steps to do it.
📌 Steps:
- First of all, go to cell D5 and insert the formula.
Here,
C5= The number to be rounded.
-2= The number of digits to which the number should be rounded.
The ROUND(C5, -2) syntax takes C5 as the number to round and “-2” is the number of digits as we want to round the nearest 100.
- Then, press ENTER and drag down the Fill Handle tool for other cells.
Finally, you will get your numbers rounded to the nearest 100, just like in the image below.
Read More: How to Round Numbers in Excel Without Formula (3 Quick Ways)
2. Utilizing ROUNDUP Function
The Excel ROUNDUP function returns a rounded number to a given number. This acts like the ROUND function, but it always rounds a number up. That means it rounded the number upward for the given number. Follow the steps to clear your confusion.
📌 Steps:
- In the beginning, move to cell D5 and write up the formula.
The ROUNDUP(C5, -2) syntax takes C5 as the number to round, and “-2” is the number of digits as we want to round the nearest 100. This function always rounds the function up to the next nearest 100.
- Then, drag it down for other cells to get the following result after pressing ENTER.
Read More: How to Round up Decimals in Excel (5 Simple Ways)
3. Applying ROUNDDOWN Function
The Excel ROUNDDOWN function returns a number rounded down to a given number. It operates like the ROUND function, except it always rounds a number down. If you have a number less than 100, then it will round it down to 0, as you want to round down to the nearest 0.
📌 Steps:
- Firstly, move to cell D5 and enter the formula.
The ROUNDUP(C5, -2) syntax takes C5 as the number to round, and “-2” is the number of digits as we want to round the nearest 100. This function rounds down the number to the nearest 100.
- Then, press ENTER and get the result like the image below.
Read More: Excel Round to 2 Decimal Places (with Calculator)
4. Employing CEILING Function
When you have to require a rounded-up number, you may utilize the CEILING function which is similar to the ROUNDUP function. The function rounds up a given number based on the specified significance. Go through the steps below to get a basic idea.
📌 Steps:
- Firstly, move to cell D5 and insert the formula.
This CEILING(C5, 100) syntax takes the number to be rounded as C5 and the significance as 100.
- Eventually, you will get the result after pressing ENTER.
Read More: Custom Number Format: Millions with One Decimal in Excel (6 Ways)
Similar Readings
- How to Use Number Format Code in Excel (13 Ways)
- [Solved] Excel Number Stored As Text
- How to Custom Number Format in Excel with Multiple Conditions
- Custom Cell Format Number with Text in Excel (4 Ways)
- How to Format a Number in Thousands K and Millions M in Excel (4 Ways)
5. Using FLOOR Function
If there is any need to round the number down, you can use the FLOOR function, which is similar to the ROUNDDOWN function. The function in Excel rounds down a certain number to the nearest multiple of a given significance. You need to follow the steps to do it.
📌 Steps:
- Primarily, input the following formula in cell D5.
This function takes the number to be rounded as C5 and the significance is 100. It rounds the number down to the below number.
Thus, you will get your desired result.
Read More: How to Round Numbers to the Nearest Multiple of 5 in Excel
6. Applying MROUND Function
The MROUND function rounds up or down a given number based on the specified multiple significance. It is similar to the ROUND function, except the ROUND function has no option to calculate based on significance. Follow the steps to do it.
📌 Steps:
- Firstly, select cell D5 and enter the formula.
- Then, press ENTER and drag it down for other cells.
Consequently, you will get the result just like the image below.
Read More: How to Round Numbers to Nearest 10000 in Excel (5 Easy Ways)
Round to the Nearest Whole Number in Excel
Sometimes, you may need to round your whole number. You can do it by applying the INT function. This function rounds the number without the fraction and displays the number of decimals.
📌 Steps:
- Firstly, select cell D5 and enter the formula.
This function rounds the whole number without the fraction.
Secondly, press ENTER and drag it down to get the following result.
Read More: Excel 2 Decimal Places without Rounding (4 Efficient Ways)
Round to the Nearest 5/1000 in Excel
Also, we can round the number to the nearest 5 by employing the CEILING function. We mentioned earlier how we can use this function. It rounds the entered number to the nearest 5 as we have entered the significance as 5. If you insert the argument 1000, it will round the number to the most immediate 1000. We will demonstrate to you the steps to get proper visualization.
📌 Steps:
- Initially, go to cell D5 and input the formula.
This function rounds the value to the nearest 5 as we insert the significance as 5.
- Then, press ENTER and you will get the following result after dragging down.
Read More: How to Round to Nearest 1000 in Excel (7 Easy Methods)
Difference Between ROUND, ROUNDUP, and ROUNDDOWN Functions
Have you noticed any differences between the ROUND, ROUNDUP, and ROUNDDOWN functions? Let’s look at the differences in our dataset.
See the output of D6, E6, and F6 cells in the following figure. The ROUND function rounds 121.56 to 100, ROUNDUP rounds it up to 200, and ROUNDDOWN rounds it down to 100.
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to round to the nearest 100 in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.
