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.
In C2, show PA for every pupil who is persistently absent (attendance of 90% or below), and leave the cell blank otherwise.
| A | B | C | |
|---|---|---|---|
| 1 | Pupil | Attendance | Flag |
| 2 | Maya Brown | 83% | |
| 3 | Leah Walker | 90% | |
| 4 | Kacper Ali | 93% | |
| 5 | Grace Wilson | 99% | |
| 6 | Aisha Ahmed | 94% | |
| 7 | Ethan Smith | 91% | |
| 8 | Daniel Wilson | 78% | |
| 9 | Evie Edwards | 83% | |
| 10 | Oliver Hall | 90% | |
| 11 | Tyrese Okafor | 96% | |
| 12 | Callum Evans | 100% | |
| 13 | Ruby Evans | 88% | |
| 14 | Oscar Edwards | 94% | |
| 15 | Mia Kowalski | 95% | |
| 16 | Mohammed Wood | 97% | |
| 17 | Nadia Evans | 89% | |
| 18 | Layla Okafor | 86% | |
| 19 | Priya Patel | 85% | |
| 20 | Isla Taylor | 92% | |
| 21 | Zainab Walker | 80% |
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
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Pupil | Year | Attendance | PA? | Band | Top of band | Band | |
| 2 | Ana | 7 | 96% | 50% | Severe | |||
| 3 | Bilal | 7 | 88% | 90% | Persistent | |||
| 4 | Chloe | 8 | 90% | 95% | At risk | |||
| 5 | Dan | 8 | 45% | 100% | Good |
=IF(C2<=0.9,"PA","")in D2. Chloe is on exactly 90%, so she counts as PA, along with Bilal and Dan.=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.=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
- In C2, show PA for every pupil who is persistently absent (attendance of 90% or below), and leave the cell blank otherwise.
- 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.
- In H1, work out the percentage of the group who are persistently absent (90% or below).
- 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.
- 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.