FormulaGym
XP 0
Streak 0

The ROUND function in Excel

ROUND tidies a number to a set number of decimal places.

What ROUND does

Positive digits round to decimal places, zero rounds to a whole number, and negative digits round to the left of the decimal point, so -1 rounds to the nearest ten. The number you pass in can be another formula, which is how it is most often used.

Syntax

=ROUND(number, num_digits)
number
The number to round. This can be a cell, a value or another formula.
num_digits
How many decimal places to keep. Use 0 for a whole number and -1 for the nearest ten.

Worked example

AB
1ItemPrice
2Pen12.45
3Pad9.99
4Ink15.20
5Clip11.10
6Average12.2
B6 =ROUND(AVERAGE(B2:B5),1)

The average is 12.185. Rounded to one decimal place it is 12.2.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Finance

Round prices and tax so totals match invoices.

Sales & Ops

Round averages for a report.

Data cleanup

Remove stray decimals from imported numbers.

Common mistakes

  • Using number formatting instead of ROUND. Formatting changes how a number looks but not its value, so totals can look off by a penny.
  • Rounding too early. Round the final result, not each step along the way.
  • Mixing up ROUND with ROUNDUP and ROUNDDOWN, which always go up or always go down.

The ROUND course

  1. 1
    Tidy up decimalsFree practice below
    Practise
  2. 2
    Round up and downComing soon
  3. 3
    Round to the nearest 5Coming soon
  4. 4
    Price rounding rulesComing soon
  5. 5
    Currency and tax mathsComing soon

Common questions

What is the difference between ROUND and formatting?

Formatting only changes what you see. ROUND changes the stored value.

How do I round to the nearest ten?

Use a negative number of digits, for example =ROUND(A1,-1).

Related functions