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.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more