Workbook Exercise

Workbook Exercise 1:
Open the “OECD for students” spreadsheet and save it to your own computer.
The aim of this exercise is to assist you to understand how to calculate the mean, median and mode
of a frequency distribution for the purposes of data analysis.
There are six tabs labelled: male science, male maths, male reading, female science, female maths
and female reading. You should now examine the data on the ‘male science’ spreadsheet. There are
three variables: rank; country and mean score. The countries are listed alphabetically. The country
variable is a nominal variable, therefore we can only calculate the mode for this variable. Each value
[country] appears only once, therefore if we created a frequency table of this variable, the first
column would list the value names [countries], the second column would list the number of
observations for each value [1] and the third column would list the percentage for each value [3.33]
and the final column would list the cumulative percentages [3.33, 6.66, 9.99 etc]. There is no mode
for this variable- each value appears only once.
The ‘science mean’ variable is an interval variable, therefore we can calculate the mode, median and
mean for this variable.
Before you can calculate the mode, median and mean you need to rank the values in order from
lowest to highest. First you must select the ‘country’ and ‘science mean’ columns. Then select ‘data’
from the top horizontal panel. Then select ‘sort’ from the drop-down box. A panel called “Sort” will
appear. On this panel, you must check that the box labelled ‘my data has headings’ or “my data has
header row” is selected. Then select the variable by which you wish to sort the data – in this case,
select ‘science mean’. This will rank the two columns of data by the value in the ‘science mean’
column when you select “OK”.
As your data is now sorted, you can rank the data on the spreadsheet. Enter ‘1’ in the column labelled
‘Rank’ next to the first country. This should be the country with the lowest mean for males for
science. Then assign the rank for each country in numerical order.
Now that you have ranked the countries, you can determine the mode and the median of the ‘science
mean’ variable. Recall that the mode is the value that appears most frequently. In this case the mode
is ‘491’ the mean value for Spain, Luxembourg and Slovak Republic. There are 30 countries in our list,
so to calculate the median you need to take the value for the 15th ranked country [Sweden] and the
16th ranked country [Hungary], add them together [504+507= 1011] and divide by 2 [505.5]. If we had
29 observations [as we do for ‘male reading’], you would simply take the value for the 15th ranked
country as our median.
To calculate the mean for the ‘science mean’ variable you need to sum all of the values and divide the
total by the number of values. To do this you write a simple formula in the cell below the final value
in the ‘science mean’ column [=SUM(C2:C31)/30] and press ‘enter’. You should now have three
measures of central tendency for the ‘science mean’ variable: the mode = ?; the median = ? and the
mean = ?.
You can explore the data further by calculating the mode, median and mean for the male ‘maths
mean’; the male ‘reading mean’; the female ‘science mean’; the female ‘maths mean’; and the female
‘reading mean’.
Based on your analysis of this data set, which country performs best overall?
How does Australia compare with the overall mean for male science, female science, male math,
female math, male reading, female reading?
Which countries would you say are most like Australia on these indicators of educational attainment?
If you have any questions about this exercise, post them on the Q & A Discussion Forum. You can also
bring queries to the drop-in session on Friday