Excel provides multiple functions for rounding numbers in one’s worksheet. The most often utilized are the ROUND function, the ROUNDUP function, the ROUNDDOWN function, the MROUND function, the CEILING function, the FLOOR function, the INT function, and some other functions. In this article, I will explain how you can use all types of Round functions in Excel.
15 Types of Round Functions in Excel
To explain this article, I have taken the following dataset. It contains some positive and some negative numbers with decimal points. I will use Round functions in Excel to round these numbers. Here, I will show you how you can use all types of Round functions in Excel to round a number.
1. Using ROUND Function
The ROUND function is used to round a number to a certain number of digits/decimal places, specified by the user. Syntax of the ROUND function is:
=ROUND(number, num_digits)
Where number is the number at hand you would like to round, and num_digits is the number of digits one wants to round the number by. Both arguments used by the function are required. For any num_digits above 0, the number is then rounded to the decimal places specified by the number of digits. If num_digits is 0 exactly, then the number is rounded to the nearest integer/whole number. If num_digits is less than 0, then the actual number is rounded to the left of the decimal point.
For your better understanding, I will explain 2 different examples of using the ROUND function.
Example-01: Rounding Number to Specified Decimal Place
In this example, I will explain how you can use the ROUND function to round a number to a specified decimal place. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to round the number. Here, I selected cell C5.
- Secondly, in cell C5 write the following formula.
=ROUND(B5,1)Â Â
Here, in the ROUND function, I selected cell B5 as the number and 1 as num_digits. The formula will return the rounded number to 1 decimal place.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula to the other cells.
Here, you can see that I have copied the formula to the other cells and got my desired rounded number to 1 decimal place.
Now, I will show you how you can round a number with 2 decimal places.
- Firstly, select the cell where you want the rounded number. Here, I selected cell D5.
- Secondly, in cell D5 write the following formula.
=ROUND(B5,2)
Here, in the ROUND function, I selected cell B5 as the number and 2 as num_digits. The formula will return the rounded number to 2 decimal places.
- Thirdly, press ENTER to get the rounded number.
- After that, drag the Fill Handle to copy the formula to the other cells like in the previous column.
At this point, I will show you how you can round a number to the nearest integer.
- Firstly, select the cell where you want the rounded number. Here, I selected cell E5.
- Secondly, in cell E5 write the following formula.
=ROUND(B5,0)
Here, in the ROUND function, I selected cell B5 as the number and 0 as num_digits. The formula will return the rounded number to 0 decimal places or you can say that it will return the nearest integer.
- Thirdly, press ENTER to get the result.
- Now, drag the Fill Handle and copy the formula to the other cells like in the previous column.
Example-02: Rounding Number to Nearest Multiple of 10/100/1000
In this example, I will show you how you can round a number to the nearest multiple of 10, 100, or 1000. Let’s see the steps.
Steps:
To begin with, I will round the number to the nearest multiple of 10.
- Firstly, select the cell where you want to round the number. Here, I selected cell C6.
- Secondly, in cell C6 write the following formula.
=ROUND(B6,-1)
Here, in the ROUND function, I selected cell B6 as the number and -1 as num_digits. The formula will round the number to the nearest multiple of 10.
- Thirdly, press ENTER to get the rounded number.
- After that, drag the Fill Handle to copy the formula to the other cells.
Finally, you can see that I have copied the formula to all the other cells and got the rounded number to the nearest multiple of 10.
Now, I will show you how you can round a number to the nearest multiple of 100.
- Firstly, select the cell where you want to round the number. Here, I selected cell D6.
- Secondly, in cell D6 write the following formula.
=ROUND(B6,-2)
Here, in the ROUND function, I selected cell B6 as the number and -2 as num_digits. The formula will round the number to the nearest multiple of 100.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle and copy the formula to the other cells like in the previous column.
At this point, I will round numbers to the nearest multiple of 1000.
- Firstly, select the cell where you want to round the number. Here, I selected cell E6.
- Secondly, in cell E6 write the following formula.
=ROUND(B6,-3)
Here, in the ROUND function, I selected cell B6 as the number and -3 as num_digits. The formula will round the number to the nearest multiple of 1000.
- Thirdly, press ENTER and you will get the result.
- Now, drag the Fill Handle and copy the formula to the other cells like in the previous column.
Moreover, the ROUNDÂ function is the most known function of all types of Round functions in Excel.
2. Use of ROUNDUP Function
The ROUNDUP function is used in cases where you want to round a number up, away from 0. This function is the most used function for rounding up a number of all types of Round functions in Excel. Syntax of the ROUNDUP function is:
=ROUNDUP(number, num_digits)
Where the number is the number at hand that you would like to round up, and the num_digits is the number of digits that you would like to round the number at hand, by. Both arguments used are required for this function. In the case where num_digits is above 0, the number is always rounded up to the decimal places specified. If num_digits is 0 exactly then the number is rounded up to the nearest integer/whole number. If num_digits is less than 0, then the number is rounded up to the left of the decimal point.
For your better understanding, I will show you an example of using the ROUNDUP function here. Let’s see the steps.
Steps:
To begin with, I will round up the numbers to one decimal place.
- Firstly, select the cell where you want to round up the number. Here, I selected cell C5.
- Secondly, in cell C5 write the following formula.
=ROUNDUP(B5,1)
Here, in the ROUNDUP formula, I selected cell B5 as the number and 1 as num_digits. The formula will round up the number to 1 decimal place.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula to the other cells.
Finally, you can see that I have copied the formula to the other cells and rounded up the numbers.
Now, I will round up the numbers to 2 decimal places.
- Firstly, select the cell where you want to round up the number. Here, I selected cell D5.
- Secondly, in cell D5 write the following formula.
=ROUNDUP(B5,2)
Here, in the ROUNDUP formula, I selected cell B5 as the number and 2 as num_digits. The formula will round up the number to 2 decimal places.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle to copy the formula to the other cells like in the column before.
At this point, I will round up the numbers to the nearest integer.
- Firstly, select the cell where you want to round up the number. Here, I selected cell E5.
- Secondly, in cell E5 write the following formula.
=ROUNDUP(B5,0)
Here, in the ROUNDUP formula, I selected cell B5 as the number and 0 as num_digits. The formula will round up the number to 0 decimal place which means the nearest integer.
- Thirdly, press ENTER to get the result.
- Now, drag the Fill Handle to copy the formula to the other cells like in the column before.
3. Applying ROUNDDOWN Function
The ROUNDDOWN function is used to round a number down towards 0. Syntax of the ROUNDDOWN function is:
=ROUNDDOWN(number, num_digits)
Where the number is the number that you would like to round down, and the num_digits is the number of digits that you would like to round the number at hand by. Both arguments are required for this function. In this case, if the num_digits value is above 0, then the number value is rounded down to the specified number of decimal places. If num_digits is exactly 0, then the number value is rounded down to the nearest integer/whole number. If num_digits is less than 0, then the number value is rounded down to the left of the decimal point.
Here, I will show you an example of using the ROUNDDOWN function so that you can understand it properly. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to round the number down. Here, I selected cell C5.
- Secondly, in cell C5 write the following formula.
=ROUNDDOWN(B5,1)
Here, in the ROUNDDOWN function, I selected cell B5 as the number and 1 as num_digits. The formula will round the number down to 1 decimal place.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula.
Finally, you can see that I have copied the formula to the other cells and rounded the numbers down.
Now, I will round the numbers down to 2 decimal places.
- Firstly, select the cell where you want to round the number down. Here, I selected cell D5.
- Secondly, in cell D5 write the following formula.
=ROUNDDOWN(B5,2)
Here, in the ROUNDDOWN function, I selected cell B5 as the number and 2 as num_digits. The formula will round the number down to 2 decimal places.
- Thirdly, press ENTER to get the result.
- Now, drag the Fill Handle to copy the formula to the other cells of the column like in the previous column.
At this point, I will round the numbers down to the nearest integer.
- Firstly, select the cell where you want to round the number down. Here, I picked cell E5.
- Secondly, in cell E5 write the following formula.
=ROUNDDOWN(B5,0)
Here, in the ROUNDDOWN function, I selected cell B5 as the number and 0 as num_digits. The formula will round the number down to 0 decimal place which means the nearest integer.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle to copy the formula to the other cells of the column like in the previous column.
4. Using MROUND Function
The MROUND function is used to round a number to a desired multiple. Syntax of the MROUND function is:
=MROUND(number, multiple)
Where the number is the value that you would like to round, and multiple refers to the nearest multiple to which you want to round the number. Both arguments are required in this function. This function is a bit different from the types of Round functions in Excel I have explained before.
Here, I will explain how you can use the MROUND function with an example. For this example, I have taken the following dataset. It contains the Number and Multiple. I will round the number to the multiple specified.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want to round the number. Here, I selected cell D5.
- Secondly, in cell D5 write the following formula.
=MROUND(B5,C5)
Here, in the MROUND function, I selected cell B5 as the number and cell C5 as the multiple. Now, the formula will round the number in cell B5 to the nearest multiple of the value in cell C5.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula.
Finally, you can see that I have copied the formula to the other cells and rounded the numbers to the nearest multiple of the specified number.
5. Employing CEILING Function
The CEILING function is used to round a number up away from zero to the nearest multiple of significance specified. Syntax of the CEILING function is:
=CEILING(number, significance)
Where the number is the number that you want to round and significance specifies the multiple to which you want to round up. Both of the arguments are required in this function.
When the number value and the multiple of significance given are the same, no rounding occurs, or if the number is a multiple of the multiple of significance. If both the number specified is negative and the significance is negative then the number value is rounded down away from 0. If the number is negative and the significance is positive, then the value is rounded up towards zero. Among all types of Round functions in Excel, this function is also used for rounding up.
Let’s see a simple example to illustrate the functionality of the CEILING function. For this example, I have taken the following dataset. It contains the Number and the Significance. Here, I will round the number up to the nearest multiple of significance.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want to round the number. Here, I selected cell D6.
- Secondly, in cell D6 write the following formula.
=CEILING(B6,C6)
Here, in the CEILING function, I selected cell B6 as the number and cell C6 as the significance. The formula will round up the number in cell B6 to the nearest multiple of the significance in cell C6.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle and copy the formula to the other cells like in the previous examples.
6. Use of CEILING.MATH Function
The CEILING.MATH function is used to round up a number to the nearest integer or to the nearest multiple of the specified significance. Syntax of the CEILING.MATH function is:
=CEILING.MATH(number, [significance], [mode])
Where the number is the number that you want to round up and significance specifies the multiple to which you want to round up. And mode specifies whether the number will be rounded towards 0 or away from 0. Here, only the number is a required argument, and both significance and mode are optional arguments. The significance is 1 by default.
Now, I will explain 3 simple examples to illustrate the functionality of CEILING.MATH function.
Example-01: Rounding Up
In this example, I will use CEILING.MATH function to round the number up to the nearest integer. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to round up the number to the nearest integer. Here, I selected cell C5.
- Secondly, in cell C5 write the following formula.
=CEILING.MATH(B5)
Here, in the CEILING.MATH function, I have selected cell B5 as the number. The formula will return the nearest integer of the number in cell B5.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle and copy the formula to the other cells like in the previous examples.
Example-02: Rounding up to Nearest Multiple of Significance
In this example, I will show you how you can use CEILING.MATH function to round a number up to the nearest multiple of significance. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to round up the number. Here, I selected cell D6.
- Secondly, in cell D6 write the following formula.
=CEILING.MATH(B6,C6)
Here, in the CEILING.MATH function, I selected cell B6 as the number and cell C6 as the significance. The formula will round up the number in cell B6 to the nearest multiple of the significance in cell C6.
- Thirdly, press ENTER to get the result.
- Now, copy the formula to the other cells of the columns by dragging the Fill Handle like in the previous examples.
Example-03: Use of CEILING.MATH Function for Negative Numbers
In this example, I will show you how you can use CEILING.MATH function for the negative numbers. For the negative numbers, the mode argument specifies the rounding direction of the number. If the mode is selected as 0 or left blank then the number is rounded up towards 0 and if the mode is selected as -1 then the number is rounded down away from 0.
Let’s see how it is done.
Steps:
To begin with, I will round up the numbers towards 0.
- Firstly, select the cell where you want to round the negative number. Here, I chose cell D6.
- Secondly, in cell D6 write the following formula.
=CEILING.MATH(B6,C6,0)
Here, in the CEILING.MATH function, I selected cell B6 as the number and cell C6 as the significance, and 0 as the mode. The function will round up the negative number towards 0.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula to the other cells like in the previous examples.
Now, I will round down the numbers away from 0.
- Firstly, select the cell where you want to round the negative number. Here, I selected cell E6.
- Secondly, in cell E6 write the following formula.
=CEILING.MATH(B6,C6,-1)
Here, in the CEILING.MATH function, I selected cell B6 as the number and cell C6 as the significance, and -1 as the mode. The function will round down the negative number away from 0.
- Thirdly, press ENTER to get the result.
- Finally, copy the formula to the other cells of the column by dragging the Fill Handle like in the previous column.
7. Employing CEILING.PRECISE Function
The CEILING.PRECISE function is used to round up a number to the nearest integer or to the nearest multiple of significance. Using this function you can round up a number regardless of the sign of the number. Syntax of the CEILING.PRECISE function is:
=CEILING.PRECISE(number, [significance])
Here, the number is the number that you want to round up and significance specifies the multiple to which you want to round up. The number is the required argument in this function, and significance is the optional argument of the function. The significance is selected as 1 by default.
Let me show you an example of using CEILING.PRECISE function for your better understanding.
Steps:
To begin with, I will round up the numbers to the nearest integer.
- Firstly, select the cell where you want to round up the number. Here, I picked cell D6.
- Secondly, in cell D6 write the following formula.
=CEILING.PRECISE(B6)
Here, in the CEILING.PRECISE function, I selected cell B6 as the number. The formula will round up the number in cell B6 and return the nearest integer.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula to the other cells like in the previous examples.
Now, I will round up the numbers to the nearest multiple of significance.
- Firstly, select the cell where you want to round up the number. Here, I selected cell E6.
- Secondly, in cell E6 write the following formula.
=CEILING.PRECISE(B6,C6)
Here, in the CEILING.PRECISE function, I selected cell B6 as the number and cell C6 as the significance. The formula will round up the number in cell B6 to the nearest multiple of the significance in cell C6.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle to copy the formula to the other cells like in the previous examples.
8. Using ISO.CEILING Function
The ISO.CEILING function is used to round up a number to the nearest integer or to the nearest multiple of significance. Using this function you can round up a number regardless of the sign of the number. Syntax of the ISO.CEILING function is:
=ISO.CEILING(number, [significance])
Here, the number is the number that you want to round up and significance specifies the multiple to which you want to round up. The number is the required argument in this function, and significance is the optional argument of the function. The significance is selected as 1 by default.
Let me show you an example to illustrate the functionality of the ISO.CEILING function.
Steps:
To begin with, I will round up the numbers to the nearest integer.
- Firstly, select the cell where you want to round up the number. Here, I chose cell D6.
- Secondly, in cell D6 write the following formula.
=ISO.CEILING(B6)
Here, in the ISO.CEILING function, I selected cell B6 as the number. The formula will round up the number in cell B6 and return the nearest integer.
- Thirdly, press ENTER to get the result.
- Now, drag the Fill Handle to copy the formula to the other cells like in the previous examples.
At this point, I will round up the numbers to the nearest multiple of significance.
- Firstly, select the cell where you want to round up the number. Here, I selected cell E6.
- Secondly, in cell E6 write the following formula.
=ISO.CEILING(B6,C6)
Here, in the ISO.CEILING function, I selected cell B6 as the number and cell C6 as the significance. The formula will round up the number in cell B6 to the nearest multiple of the significance in cell C6.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle to copy the formula to the other cells like in the previous column.
9. Use of FLOOR Function
The FLOOR Function is used to round a number down towards zero to the nearest multiple of significance specified. Syntax of the FLOOR function is:
=FLOOR(number, significance)
Where the number is the number that you want to round and significance specifies the multiple to which you want to round up. Both of the arguments are required in this function.
When the number value and the multiple of significance given are the same, no rounding occurs, or if the number is a multiple of the multiple of significance. If both the number specified and the significance is negative then the number value is rounded up towards 0. Among all types of Round functions in Excel, this function is also used for rounding down.
Let me show you a simple example for better understanding.
Steps:
- Firstly, select the cell where you want to round down the number. Here, I selected cell D6.
- Secondly, in cell D6 write the following formula.
=FLOOR(B6,C6)
Here, in the FLOOR function, I selected cell B6 as the number and cell C6 as the significance. The formula will round down the number in cell B6 to the nearest multiple of the significance in cell C6.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle and copy the formula to the other cells like in the previous examples.
10. Using FLOOR.MATH Function
The FLOOR.MATH function is used to round down a number to the nearest integer or to the nearest multiple of the specified significance. Syntax of the FLOOR.MATH function is:
=FLOOR.MATH(number, significance, mode)
Where the number is the number that you want to round down and significance specifies the multiple to which you want to round down. And mode specifies whether the number will be rounded away from 0 or towards 0. Here, only the number is a required argument, and both significance and mode are optional arguments. The significance is 1 by default.
Now, I will explain 3 simple examples to illustrate the functionality of the FLOOR.MATH function.
Example-01: Rounding Down
In this example, I will use the FLOOR.MATH function to round down the number to the nearest integer. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to round down the number to the nearest integer. Here, I selected cell C5.
- Secondly, in cell C5 write the following formula.
=FLOOR.MATH(B5)
Here, in the FLOOR.MATH function, I have selected cell B5 as the number. The formula will round down the number in cell B5 and return the nearest integer.
- Thirdly, press ENTER to get the result.
- Now, copy the formula to the other cells of the columns by dragging the Fill Handle like in the previous examples.
Example-02: Rounding Down to Nearest Multiple of Significance
In this example, I will show you how you can use the FLOOR.MATH function to round down a number to the nearest multiple of significance. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to round down the number. Here, I selected cell D6.
- Secondly, in cell D6 write the following formula.
=FLOOR.MATH(B6,C6)
Here, in the FLOOR.MATH function, I selected cell B6 as the number and cell C6 as the significance. The formula will round down the number in cell B6 to the nearest multiple of the significance in cell C6.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle to copy the formula like in the previous examples.
Example-03: Using FLOOR.MATH Function for Negative Numbers
In this example, I will show you how you can use the FLOOR.MATH function for the negative numbers. For the negative numbers, the mode argument specifies the rounding direction of the number. If the mode is selected as 0 or left blank then the number is rounded down away from 0 and if the mode is selected as -1 then the number is rounded up towards 0.
Let’s see how it is done.
Steps:
To begin with, I will round down the numbers away from 0.
- Firstly, select the cell where you want to round the negative number. Here, I selected cell D6.
- Secondly, in cell D6 write the following formula.
=FLOOR.MATH(B6,C6,0)
Here, in the FLOOR.MATH function, I selected cell B6 as the number and cell C6 as the significance, and 0 as the mode. The function will round down the negative number away from 0.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula to the other cells like in the previous examples.
Now, I will round up the numbers towards 0.
- Firstly, select the cell where you want to round the negative number. Here, I selected cell E6.
- Secondly, in cell E6 write the following formula.
=FLOOR.MATH(B6,C6,-1)
Here, in the FLOOR.MATH function, I selected cell B6 as the number and cell C6 as the significance, and -1 as the mode. The function will round up the negative number towards 0.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle to copy the formula to the other cells like in the previous column.
11. Employing FLOOR.PRECISE Function
The FLOOR.PRECISE function is used to round down a number to the nearest integer or to the nearest multiple of significance. Using this function you can round down a number regardless of the sign of the number. The syntax of the FLOOR.PRECISE function is:
=FLOOR.PRECISE(number, [significance])
Here, the number is the number that you want to round down and significance specifies the multiple to which you want to round down. The number is the required argument in this function, and significance is the optional argument of the function. The significance is selected as 1 by default.
Let me show you an example of using the FLOOR.PRECISE function for your better understanding.
Steps:
To begin with, I will round down the numbers to the nearest integer.
- Firstly, select the cell where you want to round down the number. Here, I selected cell D6.
- Secondly, in cell D6 write the following formula.
=FLOOR.PRECISE(B6)
Here, in the FLOOR.PRECISE function, I selected cell B6 as the number. The formula will round down the number in cell B6 and return the nearest integer.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula to the other cells like in the previous examples.
At this point, I will round down the numbers to the nearest multiple of significance.
- Firstly, select the cell where you want to round down the number. Here, I selected cell E6.
- Secondly, in cell E6 write the following formula.
=FLOOR.PRECISE(B6,C6)
Here, in the FLOOR.PRECISE function, I selected cell B6 as the number and cell C6 as the significance. The formula will round down the number in cell B6 to the nearest multiple of the significance in cell C6.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula like in the previous column.
12. Applying INT Function
The INT function is used to round a number down to the nearest integer. Syntax of the INT function is:
=INT(number)
Where the number is the number you want to round down to an integer. It is a required argument of the function. This function is commonly used for getting integers among all types of Round functions in Excel.
Let’s see an example to illustrate the functionality of the INT function.
Steps:
- Firstly, select the cell where you want to round the number. Here, I selected cell B5.
- Secondly, in cell B5 write the following formula.
=INT(B5)
Here, in the INT function, I selected cell B5 as the number. The formula will round down the number in cell B5 to the nearest integer.
- Thirdly, press ENTER to get the nearest integer.
- Finally, drag the Fill Handle to copy the formula like in the previous examples.
13. Using TRUNC Function
The TRUNC function is used to truncate a number to remove the fraction. Syntax of the TRUNC function is:
=TRUNC(number, [num_digits])
Where the number is the number in your hand that you want to truncate and the num_digits is a number that specifies the precision of the truncation. Here, the number is the required argument and the num_digits is the optional argument, and the default value for num_digits is 0. If the num_digits is greater than 0 then the number is truncated from the right side of the decimal point and if it is less than 0 then the number is truncated from the left side of the decimal point.
This function is quite different from the other types of Round functions in Excel.
Let’s see an example of using the TRUNC function for your better understanding.
Steps:
To begin with, I will remove the fraction part from the number.
- Firstly, select the cell where you want to return the number without a fraction. Here, I selected cell C6.
- Secondly, in cell C6 write the following formula.
=TRUNC(B6)
Here, in the TRUNC function, I selected cell B6 as the number. The formula will remove the fraction from the number in cell B6 and return the integer part.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle and copy the formula to the other cells like in the previous examples.
Now, I will show you how you can truncate a number from the right side of the decimal point.
- Firstly, select the cell where you want to truncate the number. Here, I selected cell D6.
- Secondly, in cell D6 write the following formula.
=TRUNC(B6,1)
Here, in the TRUNC function, I selected cell B6 as the number and 1 as num_digits. The function will truncate the number in cell B6 to the 1 decimal point.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle to copy the formula to other cells like in the previous examples.
Now, I will show you how you can truncate a number from the left side of the decimal point.
- Firstly, select the cell where you want to truncate the number. Here, I selected cell E6.
- Secondly, in cell E6 write the following formula.
=TRUNC(B6,-1)
Here, in the TRUNC function, I selected cell B6 as the number and -1 as num_digits. The function will truncate the number in cell B6 from the left side of the decimal point and return the nearest multiple of 10.
- Thirdly, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula to the other cells like in the previous column.
14. Employing ODD Function
The ODD function is used to round up a number to the nearest odd integer. Syntax of the ODD function is:
=ODD(number)
Where number is the number in your hand that you want to round up. The number is a required argument in this function. This function returns a odd number among all types of Round functions in Excel.
Let’s see a simple example of using the ODD function.
Steps:
- Firstly, select the cell where you want to round the number. Here, I selected cell C6.
- Secondly, in cell C6 write the following formula.
=ODD(B6)
Here, in the ODD function, I selected cell B6 as the number. The formula will round up the number in cell B6 to the nearest odd integer.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle and copy the formula to the other cells like in the previous examples.
15. Use of EVEN Function
The Even function is used to round a number up to the nearest even integer. It is one of the Round functions in Excel. Syntax of the EVEN function is:
=EVEN(number)
Where number is the number in your hand that you want to round up. The number is a required argument for this function. This function returns an even number among all types of Round functions in Excel.
Let me show you an example of using the EVEN function.
Steps:
- Firstly, select the cell where you want to round the number. Here, I selected cell C6.
- Secondly, in cell C6 write the following formula.
=EVEN(B6)
Here, in the EVEN function, I selected cell B6 as the number. The formula will round up the number in cell B6 to the nearest even integer.
- Thirdly, press ENTER to get the result.
- Finally, drag the Fill Handle to copy the formula to the other cells like in the previous examples.
Read More: 44 Mathematical Functions in Excel
Things to Remember
- You must insert a number as input in all types of Round functions in Excel. If you insert text as input, then the function will show a #VALUE! Error.
Practice Section
Here, I have provided practice sheets for you to practice the explained examples of all types of Round functions.
Download Practice Workbook
You can download the practice workbook for all types of Round functions in Excel from the following link.
Conclusion
To conclude, I tried to explain all types of Round functions in Excel. Excel provides different functions to round numbers, you can select a suitable function depending on your needs, requirements, or industry-dependent specifications. One must remember though that the rounding functions change the actual number value, so when utilized in formulas, it’s the rounded number that is input, and not the original source number. Please feel free to tell us which rounding functions you use in your spreadsheets.