The Tokyo 2020 Olympics just ended. As an audience, I saw many wonderful performances. My favorite Olympic events to watch are diving and gymnastics. How about you?
We all know that the athlete’s score is calculated on average after removing the highest and lowest scores from the judges. So, today we are going to learn how to do this in Excel?
Let’s take the picture below as an example. Column A contains the names of the contestants, and columns B through F are the scores of the five judges. Column G is the final score for each contestant.
There are two ways that can help us to count the final score.
Method 1:
Just type =(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/3 in cell G2. Then fill it.
You can keep the total score to only two decimal places.
Method 2:
We can use the TRIMMEAN Function.
Just type =TRIMMEAN(B2:F2,1/5*2) in cell G2 and fill.
How to understand the [=TRIMMEAN(B2:F2,1/5*2)]?
5 is the number of judges; 2 is the number of extremals that need to be removed because we have to get rid of the highest and the lowest scores.
At the end of this tutorial, I want to leave you with a question to think about. Suppose there are 10 judges who score, subtract 2 highest scores and 2 lowest scores, calculate the final average score, how should the formula be written?
Just comment below if you know the answer.
Leave a Reply