We’ve expanded our live Paris Olympics medal table to compare countries’ performances to their population or wealth. Along the way we explain how to cope with some problems that can arise when Excel theory hits the Real World. Our newsletter readers get a working example workbook to try themselves.
We compared population and GDP (Gross Domestic Product) to the number of
- Gold medals
- All medals (Gold, Silver, Bronze)
- Medal Score (Gold = 3, Silver = 2, Bronze = 1)
At the time of writing the clear winner is Dominica followed by St Lucia. Both have one Gold medal but small populations.
The comparisons are possible because of Excel 365’s Linked Data Types which automatically import statistics for various countries.
But it’s not as easy as you might expect because of some peculiarities in the way Linked Data Types work.
Can’t lookup a Linked Data Cell
Xlookup can’t lookup against the Linked Data Cell. You might think that it’s simple to lookup a country name against its name in Column A but you can’t.
The workaround is to make a column using the .Name linked data.
Fixing mismatched names
Country names don’t always match. This is a classic example of something that looks very smooth and easy in Microsoft demonstrations but not so great when it reaches the Real World™.
The separate country Name column helps get around the problem of different names for the same country. The Olympics refers to ‘Great Britain’ but Microsoft’s Geography data talks about the ‘United Kingdom’.
There are a few other anomalies like that ‘Republic of Ireland’ vs ‘Ireland’. But it’s not consistent, we were surprised that Microsoft’s Linked Data refers only to ‘China’ not “People’s Republic of China”.
Our workaround for this was to manually change the name of the mismatched countries so their cell in the ‘Name’ column matched the IOC Olympic name.
We did this for some countries including France which, in accordance with Olympic practice, gets an asterisk next to it as host country.
Hide an anomaly
Individual Neutral Athletes are a special IOC category. We’ve tweaked the sheet to hide this row because it makes no sense in a population or GDP comparison.
Note: when more countries are added to the medal list, the Country data table will need additions to the bottom of the table. Add to the end of the table so that any manually changed country names don’t get misplaced. The order of countries in the ‘Country data’ table doesn’t matter.
Download Bonus!
Download the complete Excel workbook with map and queries.
That gives you a headstart to make your own lists and charts. Or look at the PowerQuery steps to see how the tables where made.
The workbook includes comparisons with population or GDP (Gross Domestic Product) – works in Excel 365 for Windows / Mac only.
This download is available EXCLUSIVELY to Office Watch subscribers. The link to this and other special downloads is in each issue of Office Watch or Office for Mere Mortals.
Join today and you can get all the exclusive downloads with your first free issue.
Olympic Rankings in Excel; Gold, Silver, Bronze
Get the real Olympic logo for Office documents
Paris 2024 Olympics complete calendar in Outlook
