How to Use Excel ROUND, ROUNDUP, ROUNDDOWN, MROUND & CEILING Functions

Excel provides multiple functions for rounding numbers in one’s worksheet. The most often utilized are namely the ROUND Function, the ROUNDUP Function, the ROUNDDOWN Function, the MROUND Function and the CEILING Function.

The ROUND Function is used to round a number to a certain number of digits/decimal places, specified by the user. The syntax of the ROUND Function is:

Round Function in Excel

where number is the number at hand that 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. In the case where num_digits is less than 0, then the actual number is rounded to the left of the decimal point.

Read More: TEXT and FORMAT Functions in Excel [68 Examples]

The ROUNDUP Function is used in cases where you want to round a number up, away from 0. The syntax of the ROUNDUP Function is:

ROUNDUP Function in Excel

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.

The ROUNDDOWN Function, on the other hand, is used to round a number down towards 0. The syntax of the ROUNDDOWN Function is:

ROUNDDOWN Function in Excel

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.

The MROUND Function is used to round a number to a desired multiple. The syntax of the MROUND Function is:

MROUND Function in Excel

where the number is the value that you would like to round, and ‘multiple’ refers to the multiple to which you want to round the number. Both arguments are required.

The CEILING Function is used to round a number up away from zero to the nearest multiple of significance specified. The syntax of the CEILING Function is:

CEILING FUNCTION IN EXCEL

where the number is the number that you want to round and significance specifies the multiple to which you want to round up to.

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.

So, let’s get started with a few simple examples to illustrate the functionality of the rounding functions.

Read More: How to Use the IF & AND Functions to Test Multiple Conditions!

Using the ROUND Function

We have a list of numbers shown in our source data, that we would like to round to one decimal place, and then two decimal places using the ROUND Function. The source data is shown below.

Excel Round Function - Image 1

1) For one decimal place, in cell B4, we enter the following formula:

=ROUND(A4,1)

Excel Round Function - Image 2

2) Upon pressing CTRL-ENTER, we get a value of -0.6 returned.

Excel Round Function - Image 3

3) The value is rounded to one decimal place, specification.

4) Dragging the formula down results in the following results for the rest of the input numbers.

Excel Round Function - Image 4

5) Note the results the ROUND Function produces for one decimal place for 2.778 which is rounded to 2.8 and 2.443 which is rounded to 2.4.

6) We now want to evaluate what the effect of rounding to two decimal places using the ROUND Function will be, so in cell C4, we enter the following formula.

=ROUND(A4,2)

Excel Round Function - Image 5

7) Upon pressing CTRL-ENTER we get a value of -0.55 returned.

Excel Round Function - Image 6

8) The value is now rounded to two decimal places specification.

9) Dragging the formula results in the following results for the rest of the input numbers.

Excel Round Function - Image 7

10) Note the results the ROUND Function produces for two decimal places for 2.778 is 2.78 and for 2.443 is 2.44.

The numbers 1, 2,3,4 get rounded down when using the ROUND function, as shown for the two decimal place rounding of 2.443 to 2.44.  The numbers 5,6,7,8 get rounded up, as demonstrated for the two decimal place rounding of 2.778 to 2.78.

Read More: Top Excel Functions and Features for Management Consultants

Using the ROUNDUP Function

We have a list of numbers shown in our source data, that we would like to round up to one decimal place, two decimal places and 0 number of digits using the ROUNDUP Function. The source data is shown below.

ROUNDUP Function in Excel - Image 1

1) For one decimal place, in cell B4, enter the following formula:

=ROUNDUP(A4,1)

ROUNDUP Function in Excel - Image 2

2) Upon pressing CTRL-ENTER a value of 0.2 is returned.

ROUNDUP Function in Excel - Image 3

3) The value is now rounded up, to one decimal place specification.

4) Dragging the formula results in the following results for the rest of the input numbers.

ROUNDUP Function in Excel - Image 4

5) Note the results the ROUNDUP Function produces for one decimal place for 2.443. The value 2.443 is rounded up to 2.5 using the ROUNDUP Function whereas using the ROUND Function, rounding 2.443 to one decimal place yielded 2.4.

6) We now want to evaluate what the effect of rounding to two decimal places using the ROUNDUP Function will be, so in cell C4, we enter the following formula:

=ROUNDUP(A4,2)

ROUNDUP Function in Excel - Image 5

7) Upon pressing CTRL-ENTER, we get a value of 0.16 returned.

ROUNDUP Function in Excel - Image 6

8) The value is now rounded up to two decimal places, specification.

9) Dragging the formula results in the following results for the rest of the input numbers.

ROUNDUP Function in Excel - Image 7

10) Note the results the ROUNDUP Function produces for two decimal places for 2.443.The contrast in using the ROUNDUP Function for 2.443 is also seen, for two decimal places, since using the ROUNDUP Function for 2.443 produces 2.45 whereas using the ROUND Function for two decimal places for 2.443 produces 2.44.

The ROUNDUP Function by default rounds up.

11) For no decimal places, or 0 number of digits, in cell D4, we enter the following formula:

=ROUNDUP(A4,0)

ROUNDUP Function in Excel - Image 8

12) Upon pressing CTRL-ENTER, the value of 1 is returned as shown below.

ROUNDUP Function in Excel - Image 9

13) The value is now rounded up to the nearest integer/whole number which is 1, in this case.

14) Dragging the formula results in the following results for the rest of the input numbers.

ROUNDUP Function in Excel - Image 10

15)  Note the results the ROUNDUP Function produces for no decimal places.

Using the ROUNDDOWN Function

We have a list of numbers shown in our source data, that we would like to round down to one decimal place, two decimal places and 0 number of digits using the ROUNDDOWN Function. The source data is shown below, which is the same source as the ROUNDUP Function.

ROUNDDOWN Function in Excel - Image 1

1) For one decimal place, in cell B4, enter the following formula.

=ROUNDDOWN(A4,1)

ROUNDDOWN Function in Excel - Image 2

2) Upon pressing CTRL-ENTER a value of 0.1 is returned.

ROUNDDOWN Function in Excel - Image 3

3) The value is now rounded down to one decimal place specification.

4) Dragging the formula results in the following results for the rest of the input numbers.

ROUNDDOWN Function in Excel - Image 4

5) Note the results the ROUNDDOWN Function produces for one decimal place for 2.443, which is 2.4. Whereas, the value 2.443 is rounded up to 2.5 using the ROUNDUP Function whereas using the ROUND Function, rounding 2.443 to one decimal place yielded 2.4. Note also the results the ROUNDDOWN Function produces for one decimal place for 2.778, which is rounded down to 2.7, whereas the value 2.778 is rounded up to 2.8 by the ROUNDUP Function and 2.8 by the ROUND Function.

6) We now want to evaluate what the effect of rounding to two decimal places using the ROUNDDOWN Function will be, so in cell C4, we enter the following formula.

=ROUNDDOWN(A4,2)

ROUNDDOWN Function in Excel - Image 5

7) Upon pressing CTRL-ENTER a value of 0.15 is returned.

ROUNDDOWN Function in Excel - Image 6

8) The value is now rounded down, to two decimals places specification.

9) Dragging the formula results in the following results for the rest of the input numbers.

ROUNDDOWN Function in Excel - Image 7

10) Note the results the ROUNDDOWN Function produces for two decimal places for 2.443, which is 2.44.The contrast in using the ROUNDUP Function for 2.443 is also seen, for two decimal places, since using the ROUNDUP Function for 2.443 produces 2.45 whereas using the ROUND Function for two decimal places for 2.443 produces 2.44.

The ROUNDDOWN Function by default rounds down

11) For no decimal places, or 0 value or 0 number of digits, in cell D4, we enter the following formula:

=ROUNDDOWN(A4,0)

ROUNDDOWN Function in Excel - Image 8

12) Upon pressing CTRL-ENTER, the value of 0 is returned as shown below.

ROUNDDOWN Function in Excel - Image 9

13) The value is now rounded down to the nearest integer/whole number which is 0, in this case.

14) Dragging the formula results in the following results for the rest of the input numbers.

ROUNDDOWN Function in Excel - Image 10

15)  Note the results the ROUNDDOWN Function produces for no decimal places.

Using the MROUND Function

We have a list of numbers shown in our source data in column A, that we would like to round to the nearest multiple specified in column B. The source data is shown below.

MROUND Function in Excel - Image 1

1) So, in cell C4 enter the following formula.
=MROUND(A4,B4)

MROUND Function in Excel - Image 2

2) Upon pressing CTRL-ENTER a value of 1.2 is returned, which is the closest multiple of 0.4 going from the value of 1.1. In this case, the MROUND Function rounded up.

MROUND Function in Excel - Image 3

3) Dragging the formula results in the following results for the rest of the input numbers.

MROUND Function in Excel - Image 4

Note in the case of 2.5, the nearest multiple of 2, would be 2 (0.5 away) since the next multiple of 2 is only 4 which is 1.5 away from 2.5. In this case the MROUND Function rounded down.

Using the CEILING Function

We have a list of numbers shown in our source data in column A, that we would like to round up to the nearest multiple of significance specified in column B. The source data is shown below and is the same data used for the MROUND FUNCTION.

CEILING Function in Excel - Image 1

1) So, in cell C4 enter the following formula.

=CEILING(A4,B4)

CEILING Function in Excel - Image 2

2) Upon pressing CTRL-ENTER a value of 1.2 is returned, which is the value rounded up to the nearest multiple of significance.

CEILING Function in Excel - Image 3

3) Dragging the formula results in the following results for the rest of the input numbers.

CEILING Function in Excel - Image 4

Note in the case of 2.5, the value is rounded up to the nearest multiple of 2, which is 4. This is different to the value returned using the MROUND Function, which returned a value of 2 for 2.5, using 2 as a multiple.

And there you have it.

Download Excel Files

HowToUseRoundRoundUpRoundDownMRoundAndCeilingFunction

Conclusion

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.

Useful Links

The ROUND Function

The ROUNDUP Function

The ROUNDDOWN Function

The MROUND Function

The CEILING Function

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.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

We will be happy to hear your thoughts

Leave a reply

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.