Confident Cells

Attendance bands: practice exercises

Persistent absence, bands, PA by year group, sessions to recover. 5 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 C2, show PA for every pupil who is persistently absent (attendance of 90% or below), and leave the cell blank otherwise.

ABC
1PupilAttendanceFlag
2Maya Brown83%
3Leah Walker90%
4Kacper Ali93%
5Grace Wilson99%
6Aisha Ahmed94%
7Ethan Smith91%
8Daniel Wilson78%
9Evie Edwards83%
10Oliver Hall90%
11Tyrese Okafor96%
12Callum Evans100%
13Ruby Evans88%
14Oscar Edwards94%
15Mia Kowalski95%
16Mohammed Wood97%
17Nadia Evans89%
18Layla Okafor86%
19Priya Patel85%
20Isla Taylor92%
21Zainab Walker80%

How it works

A pupil is persistently absent when they miss 10% or more of their sessions, so attendance of 90% or below counts. Attendance is stored as a decimal shown as a percentage, so 90% is 0.9 in a formula. Severe absence is missing 50% or more: attendance of 50% or below.

Because each band includes its top value, the band table lists the highest attendance in each band. XLOOKUP(value, top values, labels, , 1) finds the exact value or the next one up. A threshold kept in a cell can be joined onto a comparison: "<="&$H$1.

=IF(C2<=0.9,"PA","") =XLOOKUP(C2,$G$2:$G$5,$H$2:$H$5,,1) =COUNTIFS(B2:B5,8,C2:C5,"<=0.9") =COUNTIF(C2:C5,"<=0.9")/COUNTA(A2:A5)

90% is 0.9

Attendance is stored as a decimal and shown as a percentage, so in a formula 90% is 0.9. Persistent absence is missing 10% or more, which is attendance of 90% or below: use <=, not <.

Worked example

ABCDEFGH
1PupilYearAttendancePA?BandTop of bandBand
2Ana796%50%Severe
3Bilal788%90%Persistent
4Chloe890%95%At risk
5Dan845%100%Good
  1. =IF(C2<=0.9,"PA","") in D2. Chloe is on exactly 90%, so she counts as PA, along with Bilal and Dan.
  2. =XLOOKUP(C2,$G$2:$G$5,$H$2:$H$5,,1) in E2. The table lists the top of each band. Match mode 1 finds the exact value or the next one up: 88% goes up to 90%, Persistent.
  3. =COUNTIFS(B2:B5,8,C2:C5,"<=0.9") in D7. COUNTIFS counts PA pupils in Year 8 only: Chloe and Dan, 2.

Thresholds in cells, and catching up

Keep the threshold in a cell and join it to the comparison: "<="&$H$1. Then changing H1 updates every count.

Getting back to target. Say a pupil has attended 88 of 100 sessions (88%) and the target is 90%. If they now attend every session, each one adds 1 to both numbers: after 10 more it's 98 out of 110 (89.1%), and after 20 it's 108 out of 120, which is exactly 90%. So they need 20 sessions without missing one.

The formula that gives this straight away is (target × sessions so far − sessions attended) ÷ (1 − target): (0.9 × 100 − 88) ÷ 0.1 = 20. Round it up, because a part session doesn't count.

What you'll practise

  1. In C2, show PA for every pupil who is persistently absent (attendance of 90% or below), and leave the cell blank otherwise.
  2. The attendance bands are in G2:H6, with the highest attendance in each band, so exactly 90% is Persistent absence. In C2, look up each pupil's band.
  3. In H1, work out the percentage of the group who are persistently absent (90% or below).
  4. In H2, count the persistently absent pupils in the year group in G2: those whose attendance is at or below the threshold in H1. Your formula will be copied down to H6.
  5. The school's attendance target is in H1. How many sessions in a row would each pupil need to attend, from now, to get back up to it? In E2, work it out as a whole number of sessions, with 0 for pupils already at or above the target.