After speaking with my podcast partner Joey about his Olympic Day and Ken Forde from WAB in Beijing about how he used Google Sheets to Live Score his Sports Day I thought I would have a go at streamlining my scoring system harnessing the power of Google Sheets to make the job of the scorekeeper simpler and also to make the results available live for parents/students who were present at the venue with access to handheld devices on the day or for the parents/grandparents/public who could not be with us at the venue.
Before I continue with the post I want you to know that prior to setting this scoresheet up the extent of my formula knowledge in Google Sheets/Excel extended only to simple =SUM formulas.
=SUMming Up the Situation - Identifying What Needed to Be Done
In individual events 10 points are awarded for 1st place, 5 points for 2nd, 3 points for 3rd and 1 point for taking part and finishing outside of the placings. In team/relay events there are double points.
What I wanted to do was make the score sheet calculate scores in real time and have this information available live to anyone who wanted to check the score.
The Live Score Sheet
You can access to original editable sheet with working formula's here:
Please Remember to Make a Copy of the Sheet and move it to your own Google Drive so that the original sheet remains intact and is visible to everyone.
Formula for Success - How I Made it Work
Calculating Individual Scores Based on Finishing Position
With this formula I wanted to make the job of the head scorer simple. All they had to do was enter the placing information (1st, 2nd, 3rd, 4th etc) and the sheet would automatically calculate how many points they had earned for their house/team.
value_if_true= The value the function returns if logical_expression is TRUE. eg. If H1=1 then "10" points
value_if_false= The value the function returns if logical_expression is FALSE eg. If H≠1 then "NOT SCORED"
Scoring for All Placings
=IF(H3=1,10,IF(H3=2,5,IF(H3=3,3,IF(H3>3,1, "NOT SCORED"))))
Adding Up Scores Based on House/Team
With this formula I wanted to calculate the total points gained by each team within each tab of the sheet. Previously I had manually gone through each tab and used a long formula (=A1+A3+A5+A7.... and so on) which took me forever to do and also meant that if I missed a cell then it would not contribute to the final score.
Here is where the =SUMIF formula comes in handy. It removed the manual work and ensures that you do not miss any cells.
The =SUMIF formula will calculate the total of a range of cells if another range meets a certain criteria. In my case I wanted to calculate the number of points gained by each team.
=SUMIF(range, criterion, [sum_range])
range= Range of Cells containing house/team identifier (House Column)
criterion= House/Team Identifier (House Column)
sum_Range= Range of Cells to be added (Points Column)
SUM of Green Team
SUM of Red Team
SUM of Blue Team
SUM of Yellow Team
Calculating Totals Across Tabs
Now that I had all of my house/team scores calculated on each tab of the sheet I wanted to add the subtotals of each house/team into a total score. To calculate across tabs on the sheet I used the following formula:
sheetName!= Name of the tab within the sheet. (eg. Event1, Event2)
cellNumber= Cell containing score (eg. House/Team Subtotal scores on each tab)
TOTAL of Green Team
TOTAL of Yellow Team
TOTAL of Red Team
TOTAL of Blue Team
Broadcasting the Scores Live
Last year I found that because of where the scorekeeper was set up (on the other side of the swimming complex due to the availability of power outlet) that I didn't have time to go back and forward to constantly check on the score. Therefore I only gave the score update 2 or 3 times throughout the day before the final winner announcement. I also had a constant stream of kids coming up to me asking what position they had finished in.
This year with the use of Google Sheets and the availability of Wifi at the pool complex I was able to check the score on my iPhone as I moved around the pool and give constant score updates throughout the day. I figured that if I could see the scores on my phone why not give the same ability to the parents/students in attendance with access to a device and even more so those parents stuck at work who couldn't make it.
To do this we sent out the following tweet from our school PE twitter account with the link to the scoresheet using a bit.ly url shortener. It was amazing to see the number of parents/students who were checking the scores and placings on their phones throughout the day and the excitement and team spirit it created throughout the day.
— ISS PhysEd (@ISSPhysEd) May 22, 2014
SUMming Up & Taking it Further with Autocrat
Overall the process worked a treat and made our Swim Gala a much more manageable and enjoyable day for everyone involved. It even allowed me to use the data I had collected and with the use of a combination of the =QUERY & =IMPORTRANGE formula's I was able to collect the first three placings in each event into a separate sheet and use theAutocrat Add On to create custom certificates for the students.
Of course it was made easier by the fact that the pool had recently installed Wifi, however if not the same process could be completed by the scorekeeper filling the scoresheet in on a connected handheld device or on a computer connected to a Hotspot from your Smartphone.
As I mentioned earlier I am happy to share my example scoresheet and formula's with the #PhysEd community, but please remember to MAKE A COPY of the original sheet before changing any of the fields to ensure that the original sheet does not get ruined. I hope this blog post makes scoring your school sports events easier and do not hesitate to contact me should you have any further questions.