Lecture notes for the Introduction to computers by James Tam

Return to the course web page

 

Practice Problem for Take Home Assignment 1 (Mahmudul Hasan)

 

The data given in the spreadsheet titled Olympic-2008-Stats.xlsx pertains to the 30 top performing countries in Beijing 2008 Olympics Games and is taken from the Olympics 2008 Dashboard.

 

This practice problem should help you to gain some idea of how you can perform analyses on a given data set to produce an informative summary.

 

Perform the following steps of analyses on the Data tab in Olympic-2008-Stats.xlsx spreadsheet: 

 
  1. Create six new columns titled Gold, Silver, Bronze, Men-Total, Women-Total, and Open/Mixed Total to contain the corresponding sums for all the countries.  

 
  1. Create a new column titled Total containing the total number of medals won by each country.  

 
  1. Sort the entire table including the newly created columns in descending order first by Gold, then by Silver if the numbers of gold medals won are equal for multiple countries, and then by Bronze if the numbers of silver medals won are equal for multiple countries.

 
  1. Based on the sorting criteria described in step 3, numerically rank the countries in a new column titled Rank. If the numbers of gold, silver, and bronze medals won by multiple countries are all equal, they should get the same rank.

 
  1. Create a new column titled Group based on the following criteria:

 

 
  1.   If majority of the medals won by a country are gold, it will belong to group “A”.

 

 
  1.  If majority of the medals won by a country are silver, it will belong to group “B”.

 

 
  1. If majority of the medals won by a country are bronze, it will belong to group “C”.

 

 

Perform the following steps of presentation on the Dashboard tab in Olympic-2008-Stats.xlsx spreadsheet:  

 
  1. Create a column chart to show the distribution of the gold, silver, and bronze medals won by the countries ranked 1 to 10.

 
  1. Create a column chart to show the performance comparison between men and women from the countries ranked 1 to 7.

 
  1. Create a pivot chart (which initially builds a column chart by default in Microsoft Word 2007) using data from the columns titled Total and Group from the Data tab. In your column chart, align the groups along X-axis, and the group size and average medal count per country in each group along Y-axis.

 

 

[Solution to the problem]

 

Please note that this practice problem only covers the analyses and presentation part of Take Home Assignment 1. To take care of all other components of the assignment, follow the assignment specification and see the examples posted at the UofC Wiki.