The attendance percentage formula
- Attendance % = sessions attended ÷ sessions held × 100
- Absence % = sessions missed ÷ sessions held × 100 (the two add up to 100% when every session has a mark)
| Example | Attended | Held | Attendance % |
|---|---|---|---|
| Weekly piano lesson, one term | 11 | 12 | 11 ÷ 12 × 100 = 91.7% |
| Football training, twice a week for 8 weeks | 13 | 16 | 13 ÷ 16 × 100 = 81.3% |
| University module | 18 | 24 | 18 ÷ 24 × 100 = 75.0% |
| Whole group of 10 people, 4 sessions | 34 marks of Present | 40 person-sessions | 34 ÷ 40 × 100 = 85.0% |
For a whole group, count person-sessions: the number of people on the roster multiplied by the number of sessions. A group of 10 that met 4 times has 40 places to fill. If 34 of them were filled, the group's rate for that period is 85%.
Herebook works out this month's rate for each person and the whole group, and you choose whether Late counts as Present and whether Excused is left out. Get it on Google Play
Decide how late, excused and cancelled sessions count
Two people using the same formula can get different answers from the same register because they count these marks differently. Pick one rule for each and write it down.
| Mark | Common choice | Effect on the rate |
|---|---|---|
| Late | Counts as attended (they were there) | Rate goes up compared with counting late as absent |
| Excused | Left out of both the top and bottom of the fraction | An excused absence does not pull the rate down |
| Excused, strict version | Counts as absent | The rate shows actual time in the room |
| Session cancelled by you | Not counted at all | Nobody is marked absent for a class that did not happen |
| Blank (no mark yet) | Not counted | Avoids treating a forgotten mark as an absence |
Example with excused absences left out
A dancer has 12 sessions in a term: 9 Present, 1 Late, 1 Excused and 1 Absent. Counting late as present and leaving excused out, the calculation is (9 + 1) ÷ (12 − 1) × 100 = 10 ÷ 11 × 100 = 90.9%. Counting excused as absent instead, it is 10 ÷ 12 × 100 = 83.3%.
Schools and universities usually set these rules in their own attendance policy. If a rate is used for a formal requirement, use the institution's definition rather than your own.
How many classes can I miss and stay above a minimum?
If a course needs a minimum rate, you can work out how many absences fit inside it.
- Multiply the total number of sessions by the minimum rate. For 40 sessions and a 75% minimum: 40 × 0.75 = 30.
- Round up to a whole session: you need to attend at least 30.
- Subtract from the total: 40 − 30 = 10 sessions can be missed.
If you are already partway through, use the sessions still to come. Say you have attended 14 of the first 20 and there are 20 left. To reach 30 in total, you need 16 of the remaining 20, so you can miss 4 more.
To find out how many sessions in a row it takes to climb back to a target, solve (attended + n) ÷ (held + n) ≥ target. From 14 of 20 (70%) to 75%: (14 + n) ÷ (20 + n) ≥ 0.75 gives n ≥ 4. Four sessions in a row bring the rate to 18 ÷ 24 = 75%.
Attendance percentage formula in Excel or Google Sheets
Put one person per row and one session per column, and type P, L, E or A in each cell. With the marks for one person in B2:M2:
| What you want | Formula |
|---|---|
| Present only | =COUNTIF(B2:M2,"P")/COUNTA(B2:M2) |
| Late counts as present | =(COUNTIF(B2:M2,"P")+COUNTIF(B2:M2,"L"))/COUNTA(B2:M2) |
| Late counts, excused left out | =(COUNTIF(B2:M2,"P")+COUNTIF(B2:M2,"L"))/(COUNTA(B2:M2)-COUNTIF(B2:M2,"E")) |
Format the cell as a percentage instead of multiplying by 100. COUNTA counts only filled cells, so empty columns for future sessions do not lower the rate. For a cancelled session, leave the whole column blank or type a code such as X and subtract COUNTIF(B2:M2,"X") from the bottom of the fraction.
The spreadsheet route works, but it is easy to break a formula when you copy a row or add a column mid-term. Check one person by hand after any change to the layout.
Reading the numbers
- A monthly rate shows a dip quickly. A rate over a whole year can hide a bad month behind several good ones.
- Small numbers swing hard. Missing one of 4 sessions is 75%; missing one of 20 is 95%. Look at the count as well as the percentage.
- Compare a person with their own earlier months before comparing them with the group.
- A falling rate is a reason to ask, not a verdict. The reason might be a new job, transport or an injury.
Let the rates add up as you take roll
With Herebook you mark Present, Absent, Late or Excused with one tap per person, and each person's page shows their rate month by month for the last six months.
Get it on Google PlayiPhone version coming soon
More guides
- Attendance Sheet Template: Layout, Codes and a Printable VersionHow to set up an attendance sheet template for a class, club or team: columns to include, attendance codes, a month layout to print or use in Excel, and common mistakes.
- Excused vs Unexcused Absences: What Counts and How to Record ThemThe difference between excused and unexcused absences, who decides, how late fits in, and how to record each one fairly in a class, club or team register.
- Sports Team Attendance Tracker: How Coaches Keep Training RecordsHow to track attendance for a sports team or club: what to record at training and matches, a simple sheet, injured and excused players, and attendance in selection.
- Church Attendance Tracking: Spreadsheet Layout and What to CountHow to set up church attendance tracking in a spreadsheet: headcounts vs named registers, Sunday school and small groups, a weekly layout, and using the numbers well.
Published by Zoranta, the developer of Herebook.