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

By Ali, Founder, Excel Lessons

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.

Read more about this

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

ROUND rounds to the number of places you give it.

In D2, type:

=ROUND(C2,2)

That rounds VAT to the nearest penny.

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

    ROUND rounds to the number of places you give it. In D2, type: =ROUND(C2,2). That rounds VAT to the nearest penny.

    Hint. The cell, a comma, then 2.

  2. 2

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

    Hint. The nearest whole number.

  3. 3

    Boxes hold 6 items, and 47 are ordered. In B9, round the boxes needed up with ROUNDUP. Seven boxes is not enough.

    Hint. ROUNDUP of the division, to 0 places.

  4. 4

    Cleaner is sold by the whole litre only. In B11, round down the litres in B10 with ROUNDDOWN.

    Hint. The cell, then 0.

  5. 5

    D5 is missing an argument, so it won't calculate. Fix D5.

    Hint. Something is missing after C5.