Are you too old to compete in the Olympics? Maybe you’re not as bendy as those 16-year-old figure skaters, but there might be other sports with athletes about your age.

Athlete bios are posted on the Vancouver 2010 Winter Games website, and I compiled that data, then created a few Excel pivot tables, to analyze the athletes’ ages.

• Which Winter Olympic sports have the oldest athletes?
• Which countries send the youngest participants?
• Do similar age groups compete in different sports?
• Who wears the wildest pants?

With our Excel pivot tables, and some pivot table grouping, we can find the answers to those pressing questions. Well, maybe not the pants issue, but let’s look at the age questions.

Show Maximum Values in a Pivot Table

Using the Olympic athlete biographical data, I created an Excel pivot table with Sport in the Row Labels area, and Age in the Values area. The default is to show the Sum of Age, which isn’t too helpful.

Instead of a sum, I’d like to see the maximum age for athletes in each sport.

• To change the summary function in an Excel pivot table Values field, right-click on one of the values in that field.
• Click Summarize Data By, then click a different summary function. I’ll click Max, to see the highest age in each sport.

Then, I can sort the list in descending order by Age, to highlight the sports with the oldest competitors.

Alpine skiing is a surprising winner, and as I expected, figure skating and short track have a much lower maximum age.

Show a Count of Athletes

Maybe there’s only one Alpine skiier, and s/he’s really old. To compare the number of athletes in each sport, I’ll add the athlete’s name field to the Values area, and it will appear as Count of Name.

Except for the last two items, there’s a good number of athletes in each sport, with Alpine Skiing as the second largest group.

See Athlete Age by Country

If we replace Sport with Nationality in the Row Labels area, we can see the maximum age and athlete count for each country.

That 51-year-old alpine skier is from Mexico, and is the only athlete from that country. Coincidentally, Great Britain sent 51 athletes, but the oldest is 45.

To see the average age per country, you can change the summary function to Average, then sort the ages in ascending order. The lowest average ages are from countries with a small number of athletes.

To see the average ages for the larger contingents, we can filter the countries by the Count of Name value. Click the drop down arrow for the Nationality field, click Value Filters, then click Top 10. I selected to see the Top 10 items by Count of Name.

The pivot table now shows only the countries with the largest number of participants, sort by average age. There’s not much difference in the average ages among countries in the top 10 list.

That’s not too encouraging! If I want to compete in the next Winter Olympics, I should move to Mexico, and take up alpine skiing.

Age Range in Selected Sports

Finally, let’s see the age range in a few of the ice sports.  I removed Nationality from the Row Labels, and added Sports. Then, I filtered the list, to show only four of the sports – curling, figure skating, ice hockey and speed skating.

Figure skating has the narrowest age range, and curling has the widest. Maybe I can stay in Canada, and learn how to curl.

Instead of showing the individual ages on the chart, I can group the ages into 5 year bands. Right-click on an Age, and click Group. Then enter 5 in the By box, and click OK.

The chart looks less like the Rocky Mountains, and it’s easier to see the age ranges for each sport.

Download the Data

I’ve saved the athlete bio data in a zipped text file that you can download, and use it to create your own pivot table. Let me know if you make any surprising discoveries.

Download the athlete data

Excel Pivot Table Tutorials

___________