Scoring Competitions & Awards in Excel: Judges' Panels, Dropping High & Low, Weighted Criteria, Judge Normalisation & Tie-Breaks

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Total and average the marks of a five-judge panel, see why a total is unfair when a mark is missing, count the missing marks per entry with COUNT and find all gaps in the sheet before results are announced.
• Drop each entry's highest and lowest mark with SUM − MAX − MIN, turn the rest into a trimmed mean, rank the entries again and see which entry one extreme mark had pushed down.
• Check that the criterion weights add up to 100 %, score every cake with SUMPRODUCT, compare the result with a simple average and score one entry judge by judge.
• Measure how strict each judge is with AVERAGEIFS and STDEV.S, centre every score on its judge's average and turn it into a z-score, so routines scored by different judges can be compared fairly.
• Turn routine times into seconds over the limit, charge a penalty per started 10 seconds after a grace period, deduct rule points, keep net scores from going negative and count the entries that lost points.
• Rank ten science-fair projects with RANK.EQ, find out how many share each total, break the ties with the originality mark scaled down by 1000 and produce a final rank without shared places.
• Find the best score in each category with MAXIFS, look up its winner with INDEX/MATCH, and build the overall top 3 with LARGE and a lookup.
• Calculate each entry's panel mean, measure how far every judge's mark lies from it, highlight the marks that are more than 1.5 away for a review and chart the final panel means.