Confident Cells

Percentages and ROUND: practice exercises

ROUND, ROUNDUP, whole-group percentages. 6 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

In D2, work out each employee's attendance: days attended divided by possible days.

ABCDEFGH
1EmployeePossible daysDays attendedAttendanceRounded %
2Grace12011495Whole group %90
3Tom12010184.2
4Priya118118100Staff on away day47
5Marek1209680Seats per minibus16
6Nia11611094.8Minibuses needed3
7Ollie120120100
8Lucy1108779.1
9Hamza12010486.7

How it works

Formatting a cell as a percentage only changes how it looks. ROUND(value, places) changes the value itself, which matters when you compare it with a target or copy it somewhere else.

ROUNDUP always rounds up, and CEILING(number, multiple) rounds up to the next multiple, such as whole boxes. A percentage change is (new − old) ÷ old.

=ROUND(D2/C2*100,1) =ROUNDUP(B2/B3,0) =CEILING(50,12)

Percentages

A percentage is a part ÷ the whole. 113 days attended out of 120 is 113 ÷ 120 = 0.9417.

  • Format the cell as a percentage and it shows 94.2%. Don't multiply by 100 as well, or it shows 9417%.
  • To get the number 94.2 itself (to compare with a target of 95, say), multiply by 100.
  • A percentage change is (new − old) ÷ old. Sales going from 40 to 50 is (50 − 40) ÷ 40 = 25%.

Worked example: ROUND

ABCDE
1NameTeamPossible daysAttendedAttendance
2AmiraSales120113
  1. =D2/C2 in . Attended ÷ possible gives a long decimal, 0.94166…. Formatting the cell as a percentage would show 94.2%, but the extra decimal places would still be there in the value.
  2. =ROUND(D2/C2*100 in . Multiply by 100 to get a number out of 100, and wrap the whole calculation in ROUND(.
  3. =ROUND(D2/C2*100,1) in . After a comma, how many decimal places to keep: 1. ROUND changes the value itself, so it's now exactly 94.2.

Rounding up

CEILING(number, multiple) rounds up to the next multiple: CEILING(50,12) is 60, the next whole number of boxes of 12.

AB
1Away day
2Staff50
3Seats per minibus16
4Minibuses needed
  1. =ROUND(B2/B3,0) in . 50 staff ÷ 16 seats = 3.125, and ROUND to 0 places gives 3. But 3 minibuses only seat 48, so two people are left behind.
  2. =ROUNDUP(B2/B3,0) in . ROUNDUP always goes up: 4 minibuses. Use it whenever part of something means you need a whole one.

What you'll practise

  1. In D2, work out each employee's attendance: days attended divided by possible days.
  2. In E2, give attendance as a number out of 100, rounded to 1 decimal place (for example 95.3).
  3. In H2, work out attendance for the whole group (total attended ÷ total possible × 100), rounded to 1 decimal place.
  4. In H6, work out how many minibuses the away day needs. You can't book part of a minibus.
  5. In D2, work out the percentage change in each person's sales from last year to this year. A fall should come out negative.
  6. Stationery comes in boxes of the number in H1. In C2, work out how many of each item to order: at least the number needed, in whole boxes.