Purpose

Return value

Syntax

=ROUND(number,num_digits)
  • number - The number to round.
  • num_digits - The place at which number should be rounded.

Using the ROUND function

The ROUND function rounds a number to a given number of places. ROUND rounds up when the last significant digit is 5 or greater, and rounds down when the last significant digit is less than 5.

ROUND takes two arguments, number and num_digits . Number is the number to be rounded, and num_digits is the place at which the number should be rounded. When num_digits is greater than zero, the ROUND function rounds on the right side of the decimal point. When num_digits is less or equal to zero, the ROUND function rounds on the left side of the decimal point. Use zero (0) for num_digits to round to the nearest integer. This behavior is summarized in the table below:

DigitsBehavior
>0Round to the nearest 0.1, 0.01, 0.001, etc.
<0Round to the nearest 10, 100, 1000, etc.
=0Round to the nearest whole number

How ROUND works

Rounding simplifies numbers by decreasing the number of digits while keeping the number close to its original value. The result is less accurate, but easier to use. The ROUND function uses a standard rounding process like this:

  1. Determine the precision. The precision is determined by the number of decimal places provided as the num_digits argument. For example, =ROUND(A1,1) will round a number in A1 to one decimal place, and =ROUND(A1,0) will round to the nearest whole number.

  2. Determine the rounding digit. This is the number in the place you are rounding to. For example, when rounding to the nearest whole number, the last number to keep is in the 1s position.

  3. Round the number based on the next number to the right . Leave the rounding digit the same if the next number to the right is less than 5. Increase the rounding digit by 1 if the next number is 5 or more. For example, to round the number 2.786 to the nearest tenth:

  • The rounding digit is 7 (the first digit after the decimal).
  • The next digit is 8.
  • Since 8 is greater than 5, round up the 7 to 8.
  • The number becomes 2.8.

We can get the same result with the ROUND function by providing 1 as num_digits :

=ROUND(2.786,1) // returns 2.8

Round to right

To round values to the right of the decimal point, use a positive number for digits:

=ROUND(A1,1) // Round to nearest tenth (0.1)
=ROUND(A1,2) // Round to nearest hundredth (0.01)
=ROUND(A1,3) // Round to nearest thousandth (0.001)
=ROUND(A1,4) // Round to nearest ten-thousandth (0.0001)

Round to left

To round down values to the left of the decimal point, use zero or a negative number for digits:

=ROUND(A1,0) // Round to nearest whole number
=ROUND(A1,-1) // Round to nearest 10
=ROUND(A1,-2) // Round to nearest 100
=ROUND(A1,-3) // Round to nearest 1000
=ROUND(A1,-4) // Round to nearest 10000

Nesting inside ROUND

Other operations and functions can be nested inside the ROUND function. For example, to round down the result of A1 divided by B1, you can ROUND in a formula like this:

=ROUND(A1/B1,0) // round result to nearest integer

Any formula that returns a numeric result can be nested inside the ROUND function.

Rounding vs. formatting

It’s important to understand that Excel can perform “visual rounding” with number formatting . When number formatting is applied to a number, Excel will display the number of places specified in the format, rounding as necessary. For example, with the value 2.786 in cell A1:

  • Excel displays 2.786 when the “General” number format is applied to A1.
  • Excel displays 2.79 when the number format “0.00” is applied to A1.
  • Excel displays 2.8 when the number format “0.0” is applied to A1.
  • Excel displays 3 when the number format “0” is applied to A1.

Number formats affect the display of a number only, and the original value is not modified. In contrast, the ROUND function will round a number and return a modified number as a final result. For details on Excel’s custom number formatting with many examples, see this article .

Other rounding functions

Excel provides several rounding functions, each with a different behavior:

  • To round with standard rules, use the ROUND function .
  • To round to the nearest multiple , use the MROUND function .
  • To round down to the nearest specified place , use the ROUNDDOWN function .
  • To round down to the nearest specified multiple , use the FLOOR function .
  • To round up to the nearest specified place , use the ROUNDUP function .
  • To round up to the nearest specified multiple , use the CEILING function .
  • To round down and return an integer only, use the INT function .
  • To truncate decimal places, use the TRUNC function .

Notes

  • The ROUND function rounds a number to a specified level of precision, determined by num_digits .
  • If the number is already rounded to the given number of places, no rounding occurs.
  • If the number is not numeric, ROUND returns a #VALUE! error.
  • If num_digits is not numeric, ROUND returns a #VALUE! error.

Purpose

Return value

Syntax

=ROUNDDOWN(number,num_digits)
  • number - The number to round down.
  • num_digits - The place at which number should be rounded.

Using the ROUNDDOWN function

The ROUNDDOWN function rounds numbers down . Unlike standard rounding, where only numbers less than 5 are rounded down, ROUNDDOWN rounds all numbers down . For example:

=ROUNDDOWN(3.999,0) // returns 3

ROUNDDOWN takes two arguments, number and num_digits . Number is the number to be rounded, and num_digits is the place at which number should be rounded. When num_digits is greater than zero, the ROUNDDOWN function rounds on the right side of the decimal point. When num_digits is less or equal to zero, the ROUNDDOWN function rounds on the left side of the decimal point. Use zero (0) for num_digits to round to the nearest integer. The table below summarizes this behavior:

DigitsBehavior
>0Round down to the nearest 0.1, 0.01, 0.001, etc.
<0Round down to the nearest 10, 100, 1000, etc.
=0Round down to the nearest whole number

How ROUNDDOWN works

The ROUNDDOWN function works like the ROUND function except that it always rounds down. The steps look like this:

  1. Determine the precision. The precision is determined by the number of decimal places provided as the num_digits argument. For example, =ROUNDDOWN(A1,1) will round a number in A1 down to one decimal place, and =ROUNDDOWN(A1,0) will round down to the nearest whole number.

  2. Determine the rounding digit. This is the number in the place you are rounding to. For example, when rounding to the nearest whole number, the last number to keep is in the 1s position.

  3. Leave the rounding digit alone and treat the remaining digits to the right as zero. For example, to round the number 2.786 to the nearest tenth (i.e. 1 decimal place):

  • The rounding digit is 7 (the first digit after the decimal).
  • Treat the remaining digits as zero (2.700)
  • The number becomes 2.7.

We can get the same result with the ROUNDDOWN function like this:

=ROUNDDOWN(2.786,0) // returns 2
=ROUNDDOWN(2.786,1) // returns 2.7
=ROUNDDOWN(2.786,2) // returns 2.78

The ROUNDDOWN function is closely related to the TRUNC function . In fact, I’m not aware of any situation where the two functions return different results. If you know of a case, please let me know .

Round to right of the decimal

To round down values to the right of the decimal point, use a positive number for num_digits :

=ROUNDDOWN(A1,1) // Round down to nearest tenth (0.1)
=ROUNDDOWN(A1,2) // Round down to nearest hundredth (0.01)
=ROUNDDOWN(A1,3) // Round down to nearest thousandth (0.001)
=ROUNDDOWN(A1,4) // Round down to nearest ten-thousandth (0.0001)

The formulas above round a number in cell A1 down to the nearest 1 decimal place, the nearest 2 decimal places, the nearest 3 decimal places, and the nearest 4 decimal places.

Round to left of the decimal

To round down values to the left of the decimal point, use zero or a negative number for digits:

=ROUNDDOWN(A1,0) // Round down to nearest 1
=ROUNDDOWN(A1,-1) // Round down to nearest 10
=ROUNDDOWN(A1,-2) // Round down to nearest 100
=ROUNDDOWN(A1,-3) // Round down to nearest 1000
=ROUNDDOWN(A1,-4) // Round down to nearest 10000

ROUNDDOWN with negative numbers

Excel’s ROUNDDOWN function always rounds numbers down towards zero. You can see this behavior in the examples below, where the number to round is given as -2.786:

=ROUNDDOWN(-2.786,0) // returns -2
=ROUNDDOWN(-2.786,1) // returns -2.7
=ROUNDDOWN(-2.786,2) // returns -2.78

Although you might think negative numbers should become “more negative”, ROUNDDOWN consistently moves toward zero.

Nesting calculations inside ROUNDDOWN

Other operations and functions can be nested inside ROUNDDOWN. For example, to round down the result of A1 divided by B1, you can use a formula like this:

=ROUNDDOWN(A1/B1,0) // round down result to nearest integer

Other rounding functions

Excel provides several rounding functions, each with a different behavior:

  • To round with standard rules, use the ROUND function .
  • To round to the nearest multiple, use the MROUND function .
  • To round down to the nearest specified place , use the ROUNDDOWN function .
  • To round down to the nearest specified multiple , use the FLOOR function .
  • To round up to the nearest specified place , use the ROUNDUP function .
  • To round up to the nearest specified multiple , use the CEILING function .
  • To round down and return an integer only, use the INT function .
  • To truncate decimal places, use the TRUNC function .

Notes

  • The ROUNDDOWN function rounds a number down to a given place by treating the remaining numbers as zero.
  • If the number is already rounded down to the given number of places, no rounding occurs.
  • If number is not numeric, ROUNDDOWN returns a #VALUE! error.
  • If num_digits is not numeric, ROUNDDOWN returns a #VALUE! error.
  • ROUNDDOWN always rounds numbers toward zero.