Excel Practice
Lessons Lesson 19

ROUND, ROUNDUP and ROUNDDOWN

About this lesson

ROUND takes a number and a number of decimal places and returns the nearest value. ROUNDUP always rounds away from zero. ROUNDDOWN always rounds towards it.

By the end you can

  • round to pennies with ROUND and to whole numbers with 0 places.
  • round up for things bought whole and down for things you already have.
  • say why rounding a value beats formatting a cell.

Practises ROUND ROUNDUP ROUNDDOWN INT

The idea

Three functions, one shape: the number, then how many decimal places to keep. ROUND goes to the nearest value. The other two are for when nearest is wrong. Boxes to order: seven and a bit means eight. Whole litres in stock: three and a bit means three.

The mistake to know is not in the formula. It is in the cell format. Formatting a cell to show two decimal places changes what you see and nothing else. The full 0.698 is still there. It is still added into every total, and the total ends up a penny away from the figures on screen. ROUND changes the value. When the pennies have to add up, round the values.

The mistake to watch for

Formatting a cell to two decimal places changes what you see and nothing else. The full value is still there and still added into every total. So the total ends up a penny away from the figures on screen. ROUND changes the value. When the pennies have to add up, round the values. And give ROUND its second argument, because without it the formula does not work at all.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

The VAT in C2 comes to 0.698, and nobody pays a tenth of a penny. In D2, round it to two decimal places with ROUND.

To begin, type it exactly:

=ROUND(C2,2)

D2
Row A B C D E F G
1 Item Ex VAT VAT Rounded
2 Notebook 3.49 0.698
3 Pens (box) 12.99 2.598
4 Stapler 8.75 1.75
5 Paper 4.19 0.838
6
7 Items per box 6
8 Items ordered 47
9 Boxes to order
10 Litres of cleaner 3.7
11 Whole litres

Every step

  1. 1

    The VAT in C2 comes to 0.698, and nobody pays a tenth of a penny. In D2, round it to two decimal places with ROUND. To begin, type it exactly: =ROUND(C2,2).

    Hint. The cell, a comma, then 2.

  2. 2

    Read =ROUND(C3,0) and say what it returns. C3 holds 2.598.

    Hint. The nearest whole number.

  3. 3

    Forty-seven items are ordered and they come in boxes of six. Seven boxes is not enough. In B9, work out how many boxes to order with ROUNDUP.

    Hint. ROUNDUP of the division, to 0 places.

  4. 4

    The cleaner is sold by the whole litre and 3.7 litres are left. In B11, show the number of whole litres with ROUNDDOWN.

    Hint. The cell, then 0.

  5. 5

    D5 was meant to round the paper's VAT to pennies, and it will not even calculate. Fix it.

    Hint. Something is missing after C5.