I watched the closing ceremonies of the Olympics last night. It was very nice…but who won the Olympics?
Gold = Silver = Bronze
Should we simply count the number of medals won and then rank the countries?
Athletes strive for gold. Gold is greater than silver, silver is greater than bronze. Shouldn’t we reflect this in medal totals?
Weighted Total using SUMPRODUCT function
Learning SUMPRODUCT initially takes some time to understand but it’s pure gold!
Define a weight for each medal and SUMPRODUCT function does the magic!
Formula in cell J9 =SUMPRODUCT($J$5:$L$5,C9:E9)
How Does It Work?
We have two ranges: J5:L5 and C9:E9. Multiply the 1st values in each range.
Gold’s weighted value of 4 X Norway’s gold count of 14 = 56. Repeat for silver and bronze.
Step By Step
Cells being multiplied: (J5 X C9) + (K5 X D9) + (L5 X E9)
Values being multiplied: (4 X 14) + (2 X 14) + (1 X 11)
Finally we have: (56) + (28) + (11)
Excel File
‘Simple Total‘ (all medals = 1) versus ‘Weighted Total‘ (explained above).
Download my Excel file and adjust the weighted values.
Am I The Only One Who Thinks This Way?
When trying to sleep I started thinking of other Olympic factors:
- population?
- why do some sports award so many medals? (i.e. swimming).
- how ‘wintery’ is a country?
- how much money is spent?
I started googling. What I discovered was intriguing!
Which country is doing best in the Winter Olympics? Sometimes, it depends how you count. Changing how much you value each medal
can change a country’s rank in the medals table.
Usually the country that has led in total medals also led in the gold count – when did it not? Exceptions are the 1896, 1912 and 1964 Summer Olympics when the United States finished first in gold medal count but second in the overall medal count. Also in 2008, the US finished first in total medal count but second to China in the overall based on gold medals. A criticism of counting only gold medals to determine success is that it implies that winning silver and bronze, or making the final, is worthless.
I wanted to develop a weighted medal count that not only adjusted for the importance of gold over silver over bronze, but also for the relative importance of one event over another.
The second assumption is that all sports should be treated as having equal importance. Swimming has a total of 34 events for its male and female participants, while basketball has only two. It doesn’t seem fair to conclude from this that swimming, as an Olympic sport, is 17 times more important than basketball, and yet, for the purposes of the medal count, that’s exactly how the two are treated. It’s simply the respective natures of the sports that cause the difference in each is organized; swimming wouldn’t make as much sense with the athletes of just two countries playing against each other, and basketball doesn’t have a series of disciplines in which its athletes can compete for multiple medals. Under COSTAS, every sport is valued equally, so the total medals for swimming are worth the same amount as the total medals for basketball. By applying weights for the number of events in each individual sport, we get a clearer picture of the relative worth of each medal.
Conclusion
Changing the weighted values affects a few countries but this year the top 3 retain their order.
I enjoyed reading alternative medal count theories (i.e. one theory tries to flatten medal count across sports considering them all to be equally important).
In the end it’s not all about winning. It’s important to learn about other cultures and share experiences. But, it’s always fun to play with numbers in Excel!
Learn With Me!
This year I promised myself I would learn Power Query (Get & Transform). I’m taking Ken Puls amazing course! Learn with me! DISCLAIMER: I’m a student and also an affiliate.
About Me
My name is Kevin Lehrbass. I’m a data analyst. This is my personal blog about Microsoft Excel.
My sister Krista DuChene (2nd from left) represented Canada in the 2016 summer Olympic games (marathon). I’m on the right wearing the white shirt. This was one of the happiest days of my life at ‘Canada House’ in Rio de Janeiro after her race.
