Statistics

Calculate Student & Workforce Absence Rate (%) & Chronic Absence Flag

Calculate absence rates from validated P, T, A and E codes, distinguish missing records from zero absence, and compare a recorded-period rate with an illustrative threshold.

Define the period and attendance codes first

Each row represents one person across five days, not five people on one day. P means present, T means tardy but counted present in this example, A means unexcused absence, and E means excused absence. Both A and E count as absent. The formula uses case-insensitive exact codes; spaces, unknown letters and numeric entries are invalid.

Blank cells and formulas returning empty text are excluded from recorded days. A blank is not evidence of attendance or an unscheduled day. Complete and reconcile missing records before reporting a scheduled-period attendance rate. No recorded days returns a message, not a misleading zero.

Calculate the unrounded rate

Enter this in G2. It validates all five positions before dividing A plus E by the number of recognized codes. Format the numeric result as a percentage; 0.4 means 40%. Do not multiply by 100 as well. COUNTBLANK includes empty text, unlike COUNTA, which can count it as an entry.

=IF((COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"T")+COUNTIF(B2:F2,"A")+COUNTIF(B2:F2,"E"))+COUNTBLANK(B2:F2)<>5,"Check attendance codes",IF((COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"T")+COUNTIF(B2:F2,"A")+COUNTIF(B2:F2,"E"))=0,"No recorded days",(COUNTIF(B2:F2,"A")+COUNTIF(B2:F2,"E"))/(COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"T")+COUNTIF(B2:F2,"A")+COUNTIF(B2:F2,"E"))))

Unexcused absence rate

This separate formula uses the same validated denominator, but counts only A. The initial result is 0.2, or 20%.

=IF((COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"T")+COUNTIF(B2:F2,"A")+COUNTIF(B2:F2,"E"))+COUNTBLANK(B2:F2)<>5,"Check attendance codes",IF((COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"T")+COUNTIF(B2:F2,"A")+COUNTIF(B2:F2,"E"))=0,"No recorded days",COUNTIF(B2:F2,"A")/(COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"T")+COUNTIF(B2:F2,"A")+COUNTIF(B2:F2,"E"))))

Compare with an illustrative threshold

Enter this in H2 after G2. Compare the raw value, not a rounded displayed percentage. The five-day example is a practice exercise, not an official chronic-absence determination.

U.S. education sources commonly describe chronic absence as missing at least 10% of school days for any reason across the relevant school period. Applicable definitions, enrollment periods and reporting rules must be checked locally. This is not a universal workforce rule or the Bradford Factor. Workplace policies require their own definitions and thresholds.

=IF(ISNUMBER(G2),IF(G2>=0.1,"At or above example threshold","Below example threshold"),G2)

Try the example

Use the original values or change them to test your understanding.