The school just had a Sports Day, successfully planned and executed, of course, but one nitpick was after an event, people wanted to know who won, and which house (Red, Green or Yellow) was in first place.
I knew it could be done easily using MS Excel, so I created a spreadsheet that can not only add up the scores and rank the houses, but also give a running account of who’s first at any point during Sports Day.
Here it is:
Note that right on top is the Overall Score and Standing. I froze the panes (select cell B5, View/Freeze Panes) so that the Overall Standing will always be visible.
The secret sauce is a RANK function and an IF function.
For instance, take the Cheer Competition.
- Adding the choreography, member participation and execution scores is easy. Just type in the scores in the respective grey boxes
- Then type =SUM(B6:B8) into cell B9 to add it up.
- Then in cell B10, type =RANK(B9,$B9:$D9,0).
- That means, Excel will look at cell B9, compare it between cells B9, C9 and D9 and rank it from highest (a “1”) to lowest (a “3”). It will give the ranking in cell B10.
- The dollar signs on the B and D means when I copy the formula from B9 to C9 and D9, the formula won’t produce an error. Try copy the formula from B10 to D10 without the dollar sign and see what happens.
- And by the way, copying a formula is simply pointing at the lower-right corner of the box. When your cursor becomes a black cross, click and drag until D10.
- The “0” means that it will rank the highest score as 1. If you type “1” instead, the lowest score will be one.
- Next, the Sports organizers wanted to give 5 points to every third place win, 10 points to every second place win and 15 points to every first place win. So under the title Points Awarded, I put in the cell F14 the formula =VLOOKUP(B14,$F$4:$G$6,2).
- It will look at cell B14, compare it to the array in F4:G6, compare it to the values in the first column and give back the value in the second column. If, for example, Red house is first place, VLOOKUP will check the array F4:G6 and give back the value 15.
- Again, the dollar signs ensure that when I copy this formula to other cells, the B14 will change, but the lookup array F4 to G6 won’t.
So, Overall Score is simply a SUM function (adding all the scores in the column Points Awarded), and Overall Standing is the same RANK function.
However, when I first tried the spreadsheet, the entire table was full of #N/A errors. Apparently, if I still haven’t typed in any scores, the RANK function will give an error message. That means we won’t have a rank until the last score, and we won’t have a running account of who’s first place.
I had to change ALL the RANK and VLOOKUP tables. It now looks like this:
That means, if the cell B16 is 0 (or in this case, empty), then put in “ “ or nothing. Else, if there is a score, Rank it.
Funny enough, the VLOOKUP still returned #N/A errors even when I added the IF function. I had to change the formula to =IF(B17=””,””,VLOOKUP(B17,$F$4:$G$6,2)). That means, if B17 is empty (and not just 0), put in “” or nothing. Else, if there is a rank, perform the VLOOKUP.
The IF statements blanked out the whole spreadsheet when no scores have been written yet, and the #N/A errors given by the RANK and VLOOKUP disappeared.
Lastly, I formatted the cells where the judges should type scores as grey, with thicker black line borders. Then I right-clicked, selected Format Cell, selected the Protection Tab, and removed the check mark on the Locked box. Actually, ALL cells, by default, will have a check on the Locked box. This Lock protection will not work unless you enable Protect Sheet (Review/Protect Sheet). That way, judges can’t accidentally type over a formula or type scores into the wrong cells.
So try it yourself! And try figure out improvements over my worksheet!