Live Scoring School Sports Events using Google Sheets

Back in May 2013 I blogged about how I was using Google Forms for Record Keeping to help me organise my upcoming Swim Gala. As I mentioned in the most recent Episode 11 of The #PhysEd Podcast I am always looking for a way to improve upon the systems that I have put in place and when this year's Swim Gala rolled around I started to think about how I could make it bigger and better.
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.

At my school students are separated into 4 houses: Newton (Yellow), Somerset (Blue), Orchard (Red) & Raffles (Green). When competing in school sports events the students earn points for their house and at the end of the event the house with the most points is the winner. All students who compete earn points and we make a big point of encouraging all students to take part in every possible event they are eligible for to ensure that their house gets maximum points. In fact last year one of the houses won by 1 single point perfectly highlighting the fact that by taking part you can help your team to win.
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.

Below is a published version of a working live score sheet similar to the one I used at my Swim Gala. You will notice the sheet has 6 different tabs across the bottom. The first 5 tabs are 5 different events (eg. Freestyle, Backstroke, Breaststroke, Butterfly & Relays) and the final tab has the final calculated scores.
You can access to original editable sheet with working formula's here:

https://docs.google.com/spreadsheets/u/1/d/1mpaSr5BNjFRX62tO5-MBWw4nfhUaoz1qRv3R8toPfrE/copy
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.

As I mentioned earlier I knew very little about formula's in Google Sheets/Excel. All it took was a little searching around for me to find the following formula's which helped me to make this score sheet a real time document that scored itself.

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.

For this job I used the =IF formula which allowed my points column to be automatically filled depending on the participant's placing.=IF(logical_expression, value_if_true, value_if_false)

 

logical_expression= An expression or reference to a cell containing an expression that represents some logical value. eg.H1=1
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"

 

=IF(H3=1, 10, "NOT SCORED")

 

With this formula I needed to beef it up a little to make sure that no matter what place the student finished their score would be automatically be calculated. To do this all you need to do is repeat the =IF pattern in the formula. You can see the final formula below which ensures that if a student finished 1st they would get 10 points, 2nd would get 5 points, 3rd would get 3 points and anyone who finished 4th and above would get 1 point.  The value_is_false = "NOT SCORED" was in place if a student failed to compete or if there was an empty lane in the race.

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
=SUMIF(F3:F38,"*G*",I3:I38)
SUM of Red Team
=SUMIF(F3:F38,"*R*",I3:I38)
SUM of Blue Team
=SUMIF(F3:F38,"*B*",I3:I38)
SUM of Yellow Team
=SUMIF(F3:F38,"*Y*",I3:I38)

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!cellNumber
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
=Event1!D1+Event2!D1+Event3!D1+Event4!D1+Event5!D1
TOTAL of Yellow Team
=Event1!F1+Event2!F1+Event3!F1+Event4!F1+Event5!F1
TOTAL of Red Team
=Event1!H1+Event2!H1+Event3!H1+Event4!H1+Event5!H1
TOTAL of Blue Team
=Event1!J1+Event2!J1+Event3!J1+Event4!J1+Event5!J1

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.

 

Keep track of the live scores here! http://t.co/a879BWjhV5 #ISSswims pic.twitter.com/0HwbcuTV8d

— ISS PhysEd (@ISSPhysEd) May 22, 2014

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.