Set up the sheet
- Column A: student or subject names. Row 1: the dates.
- Mark each cell P (present) or A (absent). Leave future dates empty.
- In the first free column, type the formula below and drag it down.
- Format the column as Percentage (Home > Number > %).
Copy-ready formulas
| You want | Formula (row 2, dates in B:Z) |
|---|---|
| Attendance % | =COUNTIF(B2:Z2,"P")/COUNTA(B2:Z2) |
| Absences so far | =COUNTIF(B2:Z2,"A") |
| Absences left at 75% (total planned classes in AB2) | =ROUNDDOWN(AB2*0.25,0)-COUNTIF(B2:Z2,"A") |
| Flag below 75% | =IF(AA2<0.75,"Below 75%","OK") |
| With 1/0 instead of P/A | =SUM(B2:Z2)/COUNT(B2:Z2) |
COUNTA counts only filled cells, so future dates do not lower the percentage. Use semicolons instead of commas if your Excel or Sheets uses a comma as the decimal separator.
Highlight low attendance
Select the percentage column, open Conditional formatting, choose "Less than" and type 0.75 with a red fill. In Google Sheets it is Format > Conditional formatting > Less than.