Live Scoring School Sports Events using Google Sheets: Part 2

Back in July, following my school swim gala I blogged about how I had successfully live scored our swim gala using Google Sheets and some fairly simple formulas.

Never being satisfied with what I have and always looking for something new and innovative I knew for our Athletics Carnival that I wanted to up the stakes even further.  Below I will take you through how I went about supercharging the Google Sheet even further to make the scoring system even more automagical.

Given that I had already worked out how to calculate scores based on finishing position, add up scores based on house/team and calculate scores across tabs, I knew that I wanted and needed to build on this further to make it work for an athletics event.
The track events would essentially be the same as a swimming race with 8 lanes and 8 participants so my existing system would work perfectly. My new challenge would be the field events where there would be a larger number of participants. Students in these events would compete as an entire grade level.
In previous years I had received feedback from parents/teachers who had been judges and scorekeepers at these events that it was alway a challenge to look down a large list of numbers scribbled on the paper scoresheet and pick out the 1st, 2nd, 3rd place getters.
So my challenge was:

Below is a modified version of the score sheet I used on the day with two tabs. One for track events and the other for field events. You can access the document with working formulas 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.
(To do this once opened click File–>Make a Copy)

The first step in finding out the best way to sort participants by distance was to find a way to identify the highest attempt for each participant. In our three field events each student would get two measured attempts. As I mentioned earlier judges and scorers had told me of the near impossible task of finding this out on a paper score sheet.
For this job I used the =MAX formula.=MAX(RANGE)
Maximum value in a numeric dataset.=MAX(E4:F4)
In my case I used the above formula which calculated the maximum value within a given range, which in this case was the Grade 1 field event. This formula took both attempts (jump/throw) for a each student and gave the maximum value in the adjacent cell.While this formula worked well to identify the best attempt for each student it still didn’t meet my original goal or sorting the students by distance. So I searched again for another formula that I could add.

Now I had the maximum distance achieved for each student I knew there must be some way that I could sort them automatically. After quite of bit of trial and error and some help from trusty Google search I found the =RANK formula.

=RANK(value, data, [is_ascending])
value – The value whose rank will be determined.
data – The array or range containing the dataset to consider.
[is_ascending] – Whether to consider the values in data in descending or ascending order. (Optional)

=RANK(G4,$G$4:$G$13,)
The above formula calculated the ranking of the score in cell G4 when compared to the other scores in G4:G13. I know what you are thinking. What’s with the $ signs in the formula? The $ sign, when used in formulas, is an absolute reference to keep a row and/or column constant in the formula.  It can precede the column reference, the row reference, or both. In this case it makes sure that when you are copying the formula down the ranking is not affected. Basically just use the $ signs and stop asking questions, it works!

This formula automatically ranked the students best attempts and produced a ranking position in the adjacent cell. I had achieved my goal of sorting the students by distance through the use of both the =MAX & =RANK formulas.

Once I had calculated the rankings I figured that I could just reuse my =IF scoring formula from before, however the ranking formula would award 1 point for a score of zero, which was a problem because if a student did not compete for some reason we did not want a point being awarded towards their team total. Luckily I solved the problem by adding a further =IF variable to my previous scoring formula.

=IF(G25=0,“NO SCORE”,IF(H25=1,10,IF(H25=2,5,IF(H25=3,3,IF(H25>3,1, “NOT SCORED”)))))
This solved my problem as now if the score for a student was zero their score column would be marked “NO SCORE.”

As I mentioned in my previous post I had very little knowledge of formulas or spreadsheets beyond the simple =SUM formulas. I know you may find that hard to believe given the exhaustive process I have gone through to make these scoresheets work for me, however it is the truth. Want to know how I did it? It’s simple. Google search and lots of trial and error. I would enter a simple search command like “how do I rank numbers in a google spreadsheet?” find a few possible solutions and then experiment. You will be surprised how far you can get!

NOTE: I am more than happy to share the above score sheet example with the #physed community however please do not make any changes to this master version. Please feel free to Make a Copy of the spreadsheet and then experiment away to your hearts content.