The ROUND function

Rounds a number to a given number of decimals. Halves round away from zero; negative decimals round to tens, hundreds and so on.

How to use ROUND

ROUND changes a number itself so it has fewer decimals, like rounding a price to the nearest cent. The second argument says how many decimals to keep: 2 for hundredths, 0 for a whole number, and a negative number to round to tens or hundreds.

Use it when the rounded value is the one you actually bill or report: VAT on an invoice line, hourly rate times hours, a percentage share of a budget. =ROUND(3.14159,2) gives 3.14, and =ROUND(1250,-2) rounds to the nearest hundred and gives 1300. A value exactly halfway rounds away from zero, so 2.5 becomes 3 and -2.5 becomes -3.

Do not confuse ROUND with cell formatting. Formatting only changes what you see, while the cell still holds every decimal. A total of formatted cells can then look off by a cent. ROUND changes the value, so the total matches the numbers on screen.

When you must always go up or always go down, such as boxes needed for an order or full teams, use ROUNDUP or ROUNDDOWN. ROUND picks the nearest number, which can leave you one short.

Syntax

ROUND(number, num_digits)
number
The number to round.
num_digits
Decimals to keep: 2 for hundredths, 0 for whole numbers, -2 for hundreds.

Examples

B1=ROUND(3.14159,2)
AB
13.14

In plain words: Round 3.14159 to 2 decimal places.

B1=ROUND(1250,-2)
AB
11300

Common ROUND mistakes

  • My total is off by a cent, but every line looks right

    The lines are only formatted to two decimals, so the hidden decimals still add up. Wrap each line in ROUND, like =ROUND(B2*C2,2), and then add up the rounded lines.

  • ROUND did not round to hundreds

    A positive second argument rounds to the right of the decimal point. For hundreds, use a negative number: =ROUND(1250,-2). Use -1 for tens and -3 for thousands.

  • ROUND gave me too few boxes, seats or teams

    ROUND goes to the nearest whole number, so 5.4 becomes 5 and the last items have no room. Use =ROUNDUP(B2,0) when anything left over still needs a whole unit.

  • ROUND returns #VALUE!

    The first argument is text, for example a number stored as text or a cell with a unit like "12 kr". Clean it with =VALUE(A2) or remove the unit, then round.

Practice ROUND

Read the guide

Questions

Does formatting a cell round the number?

No. A number format only changes what you see; the cell still holds every decimal. ROUND changes the value itself.

How do I round to the nearest whole number?

Use 0 as the second argument: =ROUND(A2,0). 7.5 rounds up to 8 and 7.4 rounds down to 7.

How do I round to the nearest 5 or 0.5?

Use MROUND, which rounds to a multiple: =MROUND(A2,5) rounds to the nearest 5 and =MROUND(A2,0.5) rounds to the nearest half.

How do I always round up or down?

Use ROUNDUP to always go up and ROUNDDOWN to always go down, with the same two arguments as ROUND: =ROUNDUP(A2,0) and =ROUNDDOWN(A2,0).

Related functions