All Types of Round Functions in Excel (15 Functions)

Get FREE Advanced Excel Exercises with Solutions!

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.

All Types of Round Functions in Excel


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)  

Using Round Function

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.

ROUND Function from All Types of Round Functions in Excel

  • 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.

Rounding Number to 2 Decimal Places by Using ROUND function from All Types of Round Function in Excel

  • After that, drag the Fill Handle to copy the formula to the other cells like in the previous column.

ROUND function in Excel for 2 Decimal Places

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.

Using ROUND function from all types of Round Functions in Excel for Nearest Integer

  • 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)

Rounding Number to Nearest Multiple of 10/100/1000

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.

Rounding a number to nearest multiple of 10 by applying ROUND function from All Types of Round Functions in Excel

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.

Rounding to Nearset Multiple of 100 by Use of ROUND function from All Types of Round Functions in Excel

  • 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.

Rounding number to Nearest Multiple of 1000 by Using ROUND function in Excel from All Types of Round Functions in Excel

  • 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)

Use of ROUNDUP Function

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.

ROUNDUP Function from All Types of Round Function in Excel

  • After that, drag the Fill Handle to copy the formula to the other cells.

Rounding Up Number to 1 Decimal Point by Using ROUNDUP Function in Excel from All Types of Round Functions

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.

Using ROUNDUP Function from All Types of Round Functions in Excel to Round Number Up to 2 Decimal Places

  • 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.

Rounding Up Numbers to Nearest Interger

  • 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)

Applying ROUNDDOWN Function

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.

Using ROUNDDOWN Function from All Types of Round Functions in Excel to Round Down a number to 1 Decimal Place

  • 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.

Using MROUND Function

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)

MROUND Function from All Types of Round Functions

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.

Rounding a Number to Multiple Specified by Using MROUND Function from All Types of Round Function in Excel

  • After that, drag the Fill Handle to copy the formula.

Dragging Fill Handle to Copy All Types of Round Functions in Excel

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.

Employing CEILING Function

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)

CEILING Function from All Types of Round Functions

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)

Use of CEILING.MATH Function

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.

CEILING.MATH function from All Types of Round Function

  • 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)

Rounding up to Nearest Multiple of Significance

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)

Use of CEILING.MATH Function from All Types of Round Functions in Excel for Negative Numbers

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)

Employing CEILING.PRECISE Function

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.

CEILING.PRECISE Function from All Types of Round Functions in Excel

  • 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.

Rounding Up to Nearest Multiple of Significance by Using CEILING.PRECiSE Function from All Types of Round Function in Excel

  • 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)

Using ISO.CEILING Function

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)

Use of FLOOR Function

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.

FLOOR Function from All Types of Round Functions

  • 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)

Using FLOOR.MATH Function

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.

Rounding Down

  • 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)

Rounding Down to Nearest Multiple of Significance

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)

Using FLOOR.MATH Function for Negative Numbers

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)

Employing FLOOR.PRECISE Function

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)

Applying INT Function

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.

INT Function from All Types of Round Functions in Excel

  • 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)

Using TRUNC Function

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.

TRUNC Function From All Types of Round Functions in Excel

  • Finally, drag the Fill Handle and copy the formula to the other cells like in the previous examples.

Truncating Fraction by Using TRUNC Function in Excel

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.

Truncating Number from the Right Side of Decimal Point by Using TRUNC function from All Types of Round Function in Excel

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.

Truncating Numbers from Lest of the Decimal Poind by Using TRUNC function from all Types of Round Functions in Excel

  • After that, drag the Fill Handle to copy the formula to the other cells like in the previous column.

Trunc function without decimal point from all types of round functions


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)

Employing ODD Function

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.

ODD Function From All Types of Round Function in Excel

  • 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)

Use of EVEN Function

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.

EVEN Function from All Types of Round Functions in Excel

  • Finally, drag the Fill Handle to copy the formula to the other cells like in the previous examples.

Result of EVEN function from all types of round functions

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.

Practice Sheet for All Types of Round Functins in Excel


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.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Taryn Nefdt
Taryn Nefdt

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo