Different rounding effects in Excel

Excel 365
When you work with digits, it's crucial how you round them. It doesn't depend on what exactly you are rounding - decimals, thousands, or millions. You can see very different amounts after summing the figures, rounded in different ways:
  • Rounded to an integer:
    Rounded to an integer in Excel 2016
  • Rounded to two decimal digits:
    Rounded to two decimal digits in Excel 2016

   1.   The functions for rounding: there are several different rounding functions in Excel:

= ROUND (<number>, <digits>)

= ROUNDUP (<number>, <digits>)

= ROUNDDOWN (<number>, <digits>)

Where:

  • <number> is an amount to round,
  • <digits> is a number of digits to round:
    • If <digits> > 0, then <number> is rounded to the specified number of decimal places.
    • If <digits> = 0, then <number> is rounded to the nearest integer.
    • If <digits> < 0, then <number> is rounded to the left of the decimal point.

   1.1.   The ROUND function rounds a number to a specified number of digits.

Rules of the rounding for ROUND function:

  • the last kept digit shouldn't be changed if the first dropped digit is less than 5,
  • the last kept digit should be increased by 1 if the first dropped digit is greater than 5.

For example:

The ROUND function in Excel 2016

   1.2.   The ROUNDUP function always rounds the number up.

Rules of the rounding for ROUNDUP function:

  • the last kept digit shouldn't be changed if the first dropped digit is 0 (zero),
  • the last kept digit should be increased by 1 if the first dropped digit is greater than 0.

For example:

The ROUNDUP function in Excel 2016

   1.3.   The ROUNDDOWN function always rounds a number down.

Rules of the rounding for ROUNDDOWN function:

  • the last kept digit shouldn't be changed.

For example:

The ROUNDDOWN function in Excel 2016

   2.   The order of rounding: rounding digits and summing them or summing digits and round the total amount.

For example:

The order of rounding in Excel 2016

   3.   Besides the formulas, Excel can display digits in different ways.

Note: How Excel works with the amount in the cell, and its formatting is only a way how it displays the value, not how it calculates the value!

For example, using cell formatting, you can see a rounded amount and unexpected formula result:

The displaying of rounding in Excel 2016

See also:

- Entering decimal points automatically.

- How to avoid misleading numbers.

See also this tip in French: Différents effets d'arrondi dans Excel.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.