Excel Rounding (Table of Contents)
Rounding in Excel
Rounding in Excel alters the actual value in a cell. In simple words, rounding means eliminating unwanted numbers and keeping the nearest numbers. A simple example will make you understand better. The rounding value of 99.50 rounding is 100. We have different kinds of rounding functions available to make rounding as per requirements.
How to Round Numbers in Excel?
Let’s understand how to Round Numbers in Excel using various functions with examples explained below.
ROUND Function
Round is the first function that strikes into our mind when we think of rounding in excel. Round function makes rounding of number as per the number of digits we want to make rounding.
Syntax of ROUND is =ROUND(number, number_digit). Find the below screenshot for reference.
Suppose there is a number 45.78956 in cell A2 and we wish to have only 2 digits after the decimal point. Apply ROUND function with number_digits as 2.
Observe the screenshot .78956 converted to 79 as we mentioned 2 in the number of digits after decimal point hence it makes rounding to nearer number .79
ROUNDUP Function
Roundup name itself suggests that this function rounds the number upwards only.
The syntax for ROUNDUP Function is =ROUNDUP(number, number_digits). Find the below screenshot for reference.
The number of digits it will take upward is depending on the number of digits we give in “number_digit” while input roundup formula.
In the above screenshot, there is only one value after the decimal point and we also give 1 in the formula hence there is no change in the results. Suppose if we increase the number of digits after the decimal point as below.
23.436 rounded as 23.5 as after decimal there should be only one digit hence it makes rounding to its upper value and make as 23.5
ROUNDDOWN Function
It is quite opposite to ROUNDUP as it is rounding to lower value and displays the number of digits mentioned by the user after the decimal point. The syntax for ROUNDUP Function is =ROUNDUP(number, number_digits). Find the below screenshot for reference.
Consider the same example which used for ROUNDUP there we got the result as 23.5, here we got the result as 23.4 as it is rounded to the lower value.
MROUND Function
MROUND function performs rounding which may go up or down depending on the given multiple in the formula.
The syntax of MROUND is =MROUND(number, multiple). Find the below screenshot for reference.
In the below example we use multiple of 5 hence the results should be multiple of 5. 50 is the nearest multiple value of 5 for 51.789
Now in the below example 55 is the nearest multiple value of 5 for 53.789.
FLOOR Function
The floor is another function that helps to round of numbers down. The syntax of FLOOR Function is =FLOOR(number, significance). Find the below screenshot for reference.
Number is what we want to do rounding and significance is the number that we want to do the multiple. In the below example we want the multiple of 3 hence we have given 3 in the place of significance. 9 is the nearest multiple of 3 for the value 10.20.
We can perform this for negative values too as told earlier it helps to down the number hence it gives the result as -12 because if we reduce -10.20 further -12 is the nearest multiple of 3 hence it is -12.
CEILING Function
CEILING is another function to rounding up the numbers. The syntax for CEILING Function is =CEILING(number, significance). Find the below screenshot for reference.
It is as similar as FLOOR Function but the difference is that is used for Round down and this is used for Roundup.
In the below example we have given the significance as 4 that means we need a rounding value greater than 21.60 and should be multiple of 4. 24 is greater than 21.6 and it is multiple of 4.
ODD Function
The ODD function is one of the rounding functions that help to find the nearest odd number of the given number.
Syntax for ODD is =ODD(number).
In the below example we applied ODD function for number 38.567. 39 is the nearest Odd number available hence it gave the result as 39.
EVEN Function
Even function also works quite similar as ODD function but the only difference is, it gives the nearest EVEN numbers after rounding.
Syntax of EVEN is =EVEN(number).
Find the below example for reference. The nearest number for 38.567 is 40.
TRUNC Function
TRUNC is another function available for rounding numbers. The syntax for TRUNCATE Function is =TRUNC(number, [number_digits])
As through the number of digits, it will eliminate numbers after the decimal point. If we do not provide any number, it will round to the nearest integer.
Observe the above screenshot, TRUNC is applied without a number of digits. 18 is the nearest integer of 18.2543.
Now we will see how it will work if we give the number of digits as 2 for the same example.
It keeps only two values after the decimal point and truncates the other digits. If we give the number a positive number, it will truncate from the right side of decimal point. If we give negative values and negative number, it will truncate from the left side of decimal point.
INT Function
INT is another function that rounds the value down to the nearest integer. Syntax of INT is =INT(number)
If we use a positive value or negative value it will always be rounding down the value. Even we use 10.7 still it downs the value to the nearest integer 10.
Perform the same for the negative value now. Observe below it further reduces the value of-10.70 to -11.
ROUNDING for TIME
Rounding can apply to time also. We will see a few examples of rounding the time. The syntax for rounding the time is =CEILING(time, “1:00”). Find the below screenshot for reference.
Observe the above formula, in the same way, I applied for FLOOR and CEILING.
If we want the rounding nearer to 20 minutes like that we have to follow a different format. Please find the below screenshot for rounding to nearest 20 minutes.
Observe the formula, the moment we give the time it will ask for hours, minutes, seconds hence we can give the nearest time details for rounding. Below is the syntax for that.
Hope you understand how to apply rounding formulas in different ways as per the situation.
Things to Remember
- ROUNDUP and ROUNDDOWN can be applied for negative numbers too.
- For CEILING and FLOOR Functions if we use positive number and negative significance then the results will be #NUM which is like an error message.
- Rounding can also be applied to time but while working with time input formula as per minutes and hours.
Recommended Articles
This has been a guide to Rounding in Excel. Here we discussed How to Round Numbers, Time using functions like ROUND, ROUNDDOWN, CEILING, FLOOR, INT, TRUNC, etc in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
