Sometimes in Excel, we want to convert the number to the nearest 100 instead of an exact value.
Rounded-off numbers are often easier to read and comprehend.
For instance, if your monthly expense is $3,587, you may want to round it off to the nearest 100 and say that expense was $3,600. While it’s good to be precise, in some situations, it’s a good idea to round numbers as it can make them easier to comprehend.
In this tutorial, I will show you can round the number to the nearest 100 in Excel.
There are various built-in functions available in Excel that can help you round a number to the nearest 100.
Round to Nearest 100 Using the ROUND Function
The ROUND function allows you to round a number to the nearest specified value.
Below I have a sample data set where I have employee names in column A and their sales in column B, and I want to round the sales to the nearest 100.
Below is the syntax of the ROUND function.
ROUND(number, num_digits)
where:
- number is the value that you want to round (these would be sales values in our example)
- num_digit – this is the digit to which you want to round the number. We will use 2 here as we want to round to the nearest 100
For rounding of the sales value done by the first representative (Michael), the formula would be
ROUND(C2,-2)
Enter the above formula in cell D2 and it will convert the sales done by Michael to the nearest 100 as shown in the screenshot below.
Now drag the formula in the entire column to convert the sales for all representatives to the nearest 100.
To drag the formula use the fill handle (+ plus icon) that appears when moving the cursor to the bottom-right of the selected cell.
Now let me quickly explain how this works.
In the ROUND function, I’ve used -2 as the second argument.
This gives us the result where the sales values have been rounded to the nearest 100.
Had we used -1, it would have rounded it to the nearest 10, and had we used 0, it would have rounded it to the nearest integer.
Also read: How to Round Numbers in Excel Without Formula?
Round to Nearest 100 Using ROUNDUP Function
If you want to round your number away from 0 you can use the ROUNDUP function, instead of the ROUND function.
For demonstration purposes, I have a data set where I have employee names in column A and their sales in column B, and I want to round up the sales to the nearest 100.
Here is the syntax of the ROUNDUP function
ROUNDUP(number,num_digits)
In order to round the sales done by the first representative (Michael) the formula would become
ROUNDUP (C2,-2)
Enter the above formula in cell D2 and it will round up the sales to the nearest 100 as shown in the screenshot.
Now copy the formula in the entire column to round up the sales of all representatives to the nearest 100.
If the number is greater than 0 ROUNDUP function will always round it to 100 if we specify num_digit = -2.
The difference between the ROUND and the ROUNDUP function is that the ROUNDUP function would always give you the higher value that meets the criteria. For example, 122 would be rounded to 100 by the ROUND function and 200 by the ROUNDUP function.
Also read: Round to the Nearest Tenth in Excel
Round to Nearest 100 Using ROUNDDOWN Function
Contrary to the ROUNDUP function, ROUNDDOWN will round down the number to the nearest 100. Let’s see it in practice.
Here is the syntax of the ROUNDDOWN function
ROUNDDOWN(number,num_digits)
In order to round the sales done by the first representative (Michael) the formula would become.
ROUNDDOWN (C2,-2)
Enter the above formula in cell D2 and It will round down the sales to the nearest 100 as shown below.
Drag the formula in the entire column to round down the sales for all representatives to the nearest 100.
The ROUNDDOWN function will always round the number between 0 – 100 to 0 if we specify num_digit = -2 as in the above example.
While rounding to the nearest 100, ROUNDUP would always give you the value that is a multiple of 100 and higher than the value that is being rounded. On the other hand, ROUNDDOWN would give you the nearest 100 value that is less than the value that is being rounded
Round to Nearest 100 using the CEILING Function
The CEILING function works similarly to the ROUNDUP function.
It rounds up the number to the nearest 10, 100, and so on.
However, in this tutorial, we are looking at rounding the number to the nearest 100 so let’s see how we can achieve this.
Here is the syntax of the CEILING function.
CEILING(number,significance)
Below is the CEILING formula that will round the sales value to the nearest 100
CEILING(C2,100)
Now enter the formula in Excel as shown below.
It will round up the sales done by Micheal to 200 as shown above.
Now drag the formula in the entire column to round up the sales done by all representatives to the nearest 100.
Tip: The Significance in the CEILING function (which is the second argument) represents the multiple to which you want to round. Here I rounded the number to the nearest 100 so I set the significance equal to 100. You can specify significance equal to 10 if you want to round the number to the nearest 10. Similarly, if you want to round the nearest integer specify a significance equal to 0.
Round to Nearest 100 using the FlOOR Function
The FLOOR function works similarly to the ROUNDDOWN function.
It rounds down the number to the nearest 10, 100, and so on.
Here is the syntax of the FlOOR function.
FLOOR(number,significance)
Below is the FLOOR formula that will round the sales value to the nearest 100
FLOOR(C2,100)
Now write the formula in Excel as shown below
Now drag the formula to the entire column in order to round the sales done by all the representatives.
The FLOOR function will always round the number between 0 – 100 to 0 if you specify the significance equal to 100.
Round to Nearest 100 Using MROUND Function
MROUND function works similarly to the ROUND function.
It returns the number rounded to a multiple of (10,100, . .).
Here is the syntax of the MROUND function.
MROUND(number,multiple)
In order to round the sales done by the first representative (Michael) the formula would be
MROUND(C2,100)
Now write the formula in Excel as shown below.
The formula will round the sales done by Micheal to the nearest 100 depending on the value. For instance, if the value to round is greater or equal to 50 it will round it to 100 otherwise 0.
Now to round the sales done by all the representatives, apply the formula to the entire column using the (+) icon available by moving the cursor to the bottom right of the selected cell.
In this tutorial, I covered different formulas to round to the nearest 100 in Excel.
If you want to round to the nearest 100, it would be best to use the ROUND or the MROUND functions. Excel also offers the ROUNDUP/ROUNDDOWN and CEILING/FLOOR function in case you always want to round up or down.
Other Excel articles you may also like:
