What it shows
Points from many events are added for each house with SUMIF, ranked and drawn as a bar chart. It shows how one list of results can produce a summary.
The science
SUMIF adds the numbers in one column only where another column matches a condition, such as a house name. Points can be calculated from the position with a rule like first 5, second 3, third 1. Keeping one raw list and calculating the scoreboard by formula means a late result can be added and everything updates.
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 20 results with event, position and house, and four house names (Red, Blue, Green, Yellow)
- 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 to D1 type Event, Position, House, Points and enter 20 results such as 100 m, 1, Red.
In D2 type =IF(B2=1,5,IF(B2=2,3,IF(B2=3,1,0))) and copy down to give points by position.
In F1 type House and G1 Total; list Red, Blue, Green and Yellow in F2 to F5.
In G2 type =SUMIF($C$2:$C$21,F2,$D$2:$D$21) and copy down to G5.
In H1 type Rank and in H2 type =RANK(G2,$G$2:$G$5,0) and copy down.
Select F1:G5 and insert a Column chart with the title Sports day points.
Check the totals by adding one house’s points by hand from the list.
Add a new result at the bottom, extend the ranges to include it, and watch the chart change.
What you should see
The scoreboard lists the four houses with their total points and rank, and the bar chart changes as new results are added.
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 SUMIF do?
It adds the values that meet a condition.
2Why keep the raw results in one list?
Totals update automatically when a result is added.
3How can you check the formula?
Add one house’s points by hand and compare.
4What if two houses tie?
RANK gives them the same rank.
Take it further
Add team events with double points using a column of multipliers and include it in the SUMPRODUCT formula.
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