What it shows
Present and absent entries are counted with COUNTIF, converted to a percentage, and flagged when below 75 percent. It shows how formulas can replace manual counting.
The science
COUNTIF counts the cells in a range that match a condition, such as the letter P. Dividing the present count by the number of days marked gives the attendance fraction, which formatted as a percentage is easy to read. Data validation limits entries to P or A, which avoids typing mistakes that would silently produce a wrong count.
Materials
Cost: free. Many items come from home or from waste; spare parts such as LEDs, motors and wire are in the store.
- A computer, laptop or phone with a browser (free)
- A spreadsheet program: LibreOffice Calc (free) or Google Sheets (free)
- A made-up list of 15 students and 20 school days
- A notebook and a pencil
Safety
This project uses safe, everyday materials. Still, read the rules below with a parent before you start.
Never use mains electricity: power models with cells, batteries or a low-voltage adapter. Sharp and hot tools, flames and lab chemicals need an adult or your teacher beside you. Keep magnets, button cells and small parts away from young children, and wash your hands after making.
All the safety rulesStep by step
8 steps. Do them in order, and test each part before you go on to the next. Write what you notice in a notebook as you go.
In A1 type Name and write dates or day numbers 1 to 20 across B1 to U1; list 15 made-up names in A2 to A16.
Select B2:U16, open Data, then Validity (Calc) or Data validation (Sheets), choose a list with the items P,A.
Fill the grid with P and A for each student using the drop-down in each cell.
In V1 type Present and in V2 type =COUNTIF(B2:U2,"P"), then copy down to V16.
In W1 type Percent and in W2 type =V2/COUNTA(B2:U2), formatted as percent, then copy down.
In X1 type Status and in X2 type =IF(W2>=0.75,"OK","Short") and copy down.
In row 18 write daily totals: in B18 type =COUNTIF(B2:B16,"P") and copy right to U18.
Add colour with conditional formatting so that Short appears in red, and find the day with the lowest attendance using MIN(B18:U18).
What you should see
The register shows each student’s present days, percentage and status, plus the number present each day, and Short is shown for anyone below 75 percent.
Viva questions
A teacher or a judge will ask how it works. Try to answer each question before you open it, then compare.
1What does COUNTIF do?
Counts the cells that meet a condition.
2Why use data validation?
So only P or A can be entered, preventing wrong counts.
3Why COUNTA for the denominator?
It counts the days actually marked, so the percentage is right part-way through the term.
4What do the daily totals show?
How many students were present each day.
Take it further
Use a pivot-like summary to show the average attendance by day of the week.
Change one thing, measure what happens, and add a table or chart of your readings: that is what turns a model into your own investigation.
Free ebooks for this project
Computer & office
MS Excel Study Book
The complete study notes, practice questions and glossary for MS Excel
Computer & office
Excel Formulas Workbook
Sixty formulas and functions, each with a worked example from school, shop or office