Exercises for Class 7 (week 7)
The main objective of this week's class is to develop skills in transforming and presenting data in Excel. Specifically:
- Create growth, index and real term variables
- Construct and evaluate histograms and frequency tables
- Formulating appropriate measures of centre and dispersion
- Construction and interpretation of scatterplots and cross-tabulations
Ex.7.1
Given the following data on the maximum speed of your cars:
154 109 137 115 152 140 154 178 101 103 126 126 137 165 165 129 200 148
(i) Using Excel, construct a frequency distribution and a histogram of the data. Use 100, 125, 150 and 175 as class intervals.
[Note: to construct histograms in Excel you will need to select Tools/Data Analysis and locate the Histogram option. If Data Analysis is not on the list you can install it by choosing Tools/Add-Ins, tick the Analysis Toolpak box and then select OK. The Data Analysis option should now be available.]
solution ishere
Ex. 7.2
The conclusion from a 40-state poll conducted by the Joint Council on Economic Education is that students do not learn enough economics. The findings were based on test results from 11th and 12th grade students who took a 46 question, multiple-choice test on basic economic concepts such as profit and the law of supply and demand. The following table gives sample data on the number of questions answered correctly.
12 10 16 24 12 14 18 23 31 14 15 19 17 9 19
28 24 16 21 13 20 12 22 18 22 18 30 16 26 18
16 14 8 25 22 15 33 24 17 19
Summarise these data in Excel using:
(i) A frequency distribution (use Data>Sort to arrange the data in order, then follow Demo 5.1). Use class intervals 0,8,16,24,32,40. Are the data ‘Normally’ distributed in a bell-shape, or are they ‘skewed’?
(ii) Calculate a cumulative frequency distribution, where each row adds all previous frequencies to the current frequency. The solution ishere. See how the formula is derived in C3 (note that you need a start value in C2).
Solutions to the first two points arehere
Ex. 7.3
Download this file to get data on UK GDP and GDP deflator.
(i) Re-base the GDP deflator series so that 1996-97 is the reference year.
(ii) Compute real GDP using the 1996-97 GDP deflators for each year (see ‘Definitions’ in the lecture,Demo 7.2, RealGDP sheet).
This is called ‘GDP in constant 1996/7 prices’.
(iii) Calculate the rate of growth of real GDP. Note that results should be identical with the real growth rate calculated inDemo 7.2 RealGDP sheet, because the use of a different base year does not alter the relative values.
Solutions are availablehere
Ex. 7.4
(Adapted from Exercise 2.1, C. Mukherjee, C.H.White and M. Wuyts, Econometrics and Data Analysis for developing countries, Routledge, 1998).
(a) Using the data file SOCECON (clickhere to download) construct histograms and compute measures of centre and dispersion for the following variables:
(i) GNP (gross national product) per capita, 1990
(ii) LEX (life expectancy), 1990
(iii) ADULT (adult literacy rate), per cent, 1990
(iv) HDI (human development index), 1990
(b) For each of the variables considered in (a) describe the main features of the distribution.
Ex 7.5
The datafile Class7qu3.xls (clickhereto download) contains survey data on voting intentions (PARTYID1) and tax and spending ideology (TAXSPEND). This information is based on the British Social Attitudes Survey 2000.
Code for Table:
PARTYID1: Conservative = 1 Labour = 2 Lib Democrat = 3
TAXSPEND: 1 = Reduce taxes and spend less on education and health
2 = Keep taxes and spending on education and health the same
3 = Increase taxes and spend more on education and health
(i) Create a two-way table (cross-tabulation) of tax and spending ideology by voting intention. [Note you can, if you wish, convert the numerical codes to text before doing this.]
(ii) What percent of Labour supporters wish taxes to increase and spending on education and health to increase?
Solutions
Exercise 7.41, exercise 7.53