ROUND, ROUNDUP and ROUNDDOWN
=ROUND(A2,2) rounds to 2 decimal places, the usual way: 2.345 becomes 2.35. ROUNDUP always goes up and ROUNDDOWN always goes down, whatever the next digit is. The second number says how many decimal places to keep.
The three, side by side
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Number | ROUND to 1 dp | ROUNDUP to 1 dp | ROUNDDOWN to 1 dp |
| 2 | 2.712 | 2.7 | 2.8 | 2.7 |
| 3 | 2.789 | 2.8 | 2.8 | 2.7 |
| 4 | 2.75 | 2.8 | 2.8 | 2.7 |
The second number can be:
- 2: to the penny. VAT is rounded like this.
- 0: to a whole number.
- −2: to the nearest hundred.
=ROUND(1234.5,-2)gives 1200.
When to round up
Whenever part of something isn't enough. 130 people on a trip, and each coach takes 52:
- 130 ÷ 52 = 2.5 coaches.
- Half a coach isn't possible, and 2 would leave people behind, so you need 3.
=ROUNDUP(B2/$F$1,0)
The same goes for boxes, packs, staff and minimum orders. ROUNDDOWN suits the opposite: how many whole packs can you make from what's in stock.
Rounding or formatting?
The "decrease decimal" button only changes how a number looks. 2.345 still shows as 2.35, but the cell still holds 2.345, and totals use the full value. That's why a column of rounded-looking figures can add up to a penny more or less than you expect. If the rounded figure is the real answer (an invoice, a grade), round it with a formula.
Common mistakes
Forgetting the second number
ROUND needs it: Excel won't accept =ROUND(A2). Use 0 for whole numbers.
Rounding too early
Rounding each step of a long calculation lets small errors build up. Round once, at the end, unless each line really is rounded (like VAT on an invoice line).
Rounding to steps like 0.5 or 5
For the nearest half day or nearest 5, use MROUND, CEILING or FLOOR: =CEILING(4.2,0.5) gives 4.5 (see holiday entitlement).
Try it now
Each coach seats the number of people in G1. In C2, work out how many coaches each trip needs, so that nobody is left behind. Your formula will be copied down to C7.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Trip | People | Coaches | Seats per coach | 52 | ||
| 2 | Museum | 130 | |||||
| 3 | Theatre | 52 | |||||
| 4 | Zoo | 75 | |||||
| 5 | Beach | 160 | |||||
| 6 | Castle | 104 | |||||
| 7 | Gallery | 30 |