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
=ROUND(13.14159,22)| A | B | |
|---|---|---|
| 1 | 3.14 |
In plain words: Round 13.14159 to 22 decimal places.
| A | B | |
|---|---|---|
| 1 | 1300 |
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).