Attendance percentage in Excel, and persistent absence
Attendance is the sessions attended divided by the sessions possible: =C2/B2, shown with the % format. A pupil who attended 153 of 170 sessions has 90% attendance, which counts as persistently absent (PA), because PA means missing 10% or more.
Attendance percentage
There are two sessions a day, morning and afternoon. Maya could have attended 170 sessions and attended 141.
- 141 ÷ 170 = 0.829…
- Shown as a percentage, 82.9%.
=C2/B2
Click the % button (or press Ctrl+Shift+5). There's no need to multiply by 100: see percentages in Excel.
Flagging persistent absence
A pupil is persistently absent at 90% attendance or below, and severely absent at 50% or below. IF shows a flag:
=IF(D2<=0.9,"PA","")
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Pupil | Possible | Attended | Attendance | Flag |
| 2 | Maya | 170 | 141 | 82.9% | PA |
| 3 | Leah | 170 | 153 | 90% | PA |
| 4 | Omar | 170 | 166 | 97.6% | |
| 5 | Zara | 170 | 80 | 47.1% | PA |
For more than two labels, such as Severe, PA and Good, use a band table and a lookup: the attendance bands lesson shows how.
The share of a group who are PA
Count the pupils at 90% or below with COUNTIF, then divide by the number of pupils:
=COUNTIF(D2:D31,"<=0.9")/COUNTA(A2:A31)
Common mistakes
Using < instead of <=
A pupil on exactly 90% is persistently absent. D2<0.9 misses them.
Typing 90 instead of 0.9
The % format shows 0.9 as 90%, but the value underneath is 0.9. D2<=90 flags everyone. Use 0.9 or 90%.
Dividing the wrong way round
=B2/C2 gives over 100%. It's attended divided by possible.
Try it now
In D2, work out each pupil's attendance: the sessions they attended as a share of the sessions possible. Column D is set to show percentages. Your formula will be copied down to D7.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Pupil | Possible | Attended | Attendance |
| 2 | Maya | 170 | 141 | |
| 3 | Leah | 170 | 153 | |
| 4 | Omar | 170 | 166 | |
| 5 | Zara | 170 | 80 | |
| 6 | Ivy | 164 | 160 | |
| 7 | Noah | 158 | 130 |