Md. Abdul Kader
In certain circumstances, we prefer the rounded or the approximate number rather than the exact number for making communication easier. For example, the exact population of New York is 8,253,213. But we like to say it is around 8 million.
In this article, you’ll get six quickest ways to round any given number to the nearest hundred (100).
Download Practice Workbook
Excel Round to Nearest 100
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.
1. Round Function
Firstly, we may utilize the ROUND, one of the most popular functions used for rounding any number.
The function returns a number rounded to a number of digits. The syntax of the function is
There are the following arguments in the function.
number – The number to round.
num_digits – The number of digits to which number should be rounded.
Num_digits(number of digits) are different based on your criteria. It will be “-2” for rounding to the nearest 100.
Let’s apply the function to our dataset. For this, select a blank cell e.g., D5.
Then type the formula =ROUND(C5, -2) where C5 is the number to round and “-2” is the number of digits as we want to round the nearest 100.
Lastly, press Enter. And you’ll get the output like the following-
2. 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.
The syntax of the function is
There are the following arguments in the function.
number – The number to round up.
num_digits – The number of digits to which number should be rounded up.
If you want to apply the function, select a blank cell e.g. D5.
Then type the formula like =ROUNDUP(C5,-2) where C5 is the unit price to round and “-2” is the number of digits as we want to round the nearest 100.
Finally, press Enter.
3. 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. The syntax of the function is
There are the following arguments in the function.
number – The number to round down.
num_digits – The number of digits to which number should be rounded down.
Let’s see what happens after applying the function in our dataset.
For that, choose a blank cell e.g. D5 and insert the formula =ROUNDUP(C5,-2) where C5 is the unit price to round and “-2” is the number of digits as we want to round the nearest 100.
In the end, press Enter.
Note. you have an alternative way to use the three functions i.e. ROUND, ROUNDUP, and ROUNDDOWN in another way. For example, the formula for ROUND function will be
If you want to use the other two functions, change only the function keeping the same format.
Obviously, you’ll get the same output as the first way.
4. 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. The syntax of the function is
There are the following arguments in the function.
number – The number that should be rounded.
significance – The multiple to use when rounding.
Let’s look at the application of the function in our dataset.
In the beginning, select a blank cell e.g. D5.
Then insert the formula =CEILING(C5, 100) where C5 is the price to round and “100” is for rounding it to the nearest 100.
Lastly, press Enter.
5. FLOOR Function
If there is any need to rounding the number down, you can use the FLOOR which is similar to the ROUNDDOWN function. The function in Excel rounds down a certain number to the nearest multiple of a given significance. The syntax of the function is
There are the following arguments in the function.
number – The number that should be rounded.
significance – The multiple to use when rounding.
If you want to apply the function to round to the nearest 100, select a blank cell and insert the formula =FLOOR(C5, 100) where C5 is the price to round and “100” is for rounding it to the nearest 100.
Then press Enter.
6. 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. The syntax of the function is
There are the following arguments in the function.
number – The number that should be rounded.
significance – The multiple to use when rounding.
If you want to apply the function for rounding the unit price to the nearest 100, select a blank cell e.g. D5.
Then insert the formula =MROUND(C5, 100) where C5 is the price to round and “100” is for rounding it to the nearest 100.
Lastly, press Enter.
Things to Keep in Mind
Difference between ROUND, ROUNDUP, and ROUNDDOWN
Have you noticed any difference between 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. ROUND function rounds 121.56 to 100, ROUNDUP rounds up it to 200 whether ROUNDDOWN rounds down it to 100.
So be careful about the differences among the functions. And as usual, you have to be cautious about file name, file location, and extension name of excel file.
Conclusion
Now, you’ve got these quickest ways with their differences. So choose one or more according to your criteria. If you have any suggestions or confusion, please let me know. Sharing knowledge will improve us all undoubtedly.
Thanks for being with me.
