How to Calculate Attendance Rate in Excel

Turn a P/A/L attendance register into attendance rates for a student and a whole class, including a policy that counts lates as half.

You will learn to

• Count days present from a register of codes
• Calculate a class-wide attendance rate
• Apply a weighted attendance policy

How it works

Attendance rate is days present divided by days recorded. With a register that uses codes such as P (present), A (absent) and L (late), count the days that are not absences with COUNTIF and divide by COUNTA: =COUNTIF(B6:K6,"<>A")/COUNTA(B6:K6). Format the result as a percentage.

The same formula over the whole grid gives the class attendance rate. If your school counts lateness differently — for example, a late arrival as half a day — weight the codes: (COUNTIF(…,"P")+0.5*COUNTIF(…,"L"))/COUNTA(…).

=COUNTIF(B6:K6,"<>A")/COUNTA(B6:K6)