Math & Statistical
Calculate Percent Absent & Attendance Rate Ignoring Unmarked Days
Compute exact student or employee absence percentage COUNTIF(B2:G2, "A") / COUNTA(B2:G2) and present/excused attendance rate while ignoring blank unrecorded future sessions.
Why dividing by total columns penalizes students for future unrecorded classes!
Classroom gradebooks and shift rosters pre-allocate columns for every session of the term—marking "P" (Present), "A" (Unexcused Absent), or "E" (Excused), while leaving future dates or cancelled holidays blank! Dividing COUNTIF(B2:G2, "A") by a hardcoded session count like 6 understates current absence rate mid-semester, whereas dividing Present count by 6 artificially penalizes everyone before future classes occur!
Using COUNTA(B2:G2) in the denominator dynamically counts ONLY recorded sessions—and guarding COUNTA(B2:G2) = 0 prevents #DIV/0! on newly enrolled students with zero recorded sessions!
| Student Attendance Row (6 Columns) | Recorded Sessions COUNTA | Unexcused "A" Count | Absence % & Attendance % |
|---|---|---|---|
| P, A, P, P, A, [Blank Future] | 5 recorded days | 2 absences | 40.0% Absent / 60.0% Present |
| P, P, E, P, P, P (1 Excused) | 6 recorded days | 0 unexcused "A" | 0.0% Unexcused Absent |
| All 6 Blank (New Transfer) | 0 recorded days | 0 | "No Recorded Sessions" |
Unexcused Absence Percentage Across Recorded Sessions (%)
Divides COUNTIF(B2:G2, "A") by COUNTA(B2:G2) * 100, ignoring blank future cells.
=IF(COUNTA(B2:G2) = 0, "No Recorded Sessions", ROUND(100 * COUNTIF(B2:G2, "A") / COUNTA(B2:G2), 1))Present + Excused Attendance Rate (%)
Sums "P" and "E" credits across recorded sessions so excused medical days do not count as truancy.
=IF(COUNTA(B2:G2) = 0, "No Recorded Sessions", ROUND(100 * (COUNTIF(B2:G2, "P") + COUNTIF(B2:G2, "E")) / COUNTA(B2:G2), 1))Try the example
Use the original values or change them to test your understanding.