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.
=SUMming Up the Situation – Identifying What Needed to Be Done
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:
The Score Sheet
(To do this once opened click File–>Make a Copy)
Taking it to the =MAX
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.
=RANKing the Results
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)
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.
Scoring based on =RANKings
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.”
Finding a Solution to a Simple Problem
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.