You might find that you read a newspaper or magazine article that contains a chart that you think you’d like to use in some way. One problem is that the chart is in the form of an image and the data on which it’s based is not available. It happens to me a few times a year.

This post then is concerned with turning the image of a chart into a table of data and then back into the chart … plus, plus. Read on to read a case study I have just prepared!

Case Study: recreating charts: with analysis

The purpose of this case study is for readers to extract and analyse data from a bar chart and data table. The case can be used in a wide variety of settings from secondary/high school level to undergraduate and professional examination level.

Having required the reader to recreate a chart from a published source, they are then required to comment on the new charts they create. In addition they are then required to discuss the possibility of an outlier being found in the data and comment on how such an outlier might be dealt with.

Finally, the reader is required to consider the situation in which the outlier is left in the data set and comment on the data set without adjustment.

Required

1 From the following chart, taken from The Economist, set up a spreadsheet file to achieve the following

clip_image001clip_image003

a) Extract the data from the bars in the chart

b) Recreate the chart from The Economist in your file

c) Create a further chart to illustrate the percentage of domestic consumption data

2 Comment on the charts you have prepared in task 1

3 a) Prepare a third chart with TeraWatt Hours on the X axis and % of Domestic data on the Y axis and comment on your new chart.

b) In your new chart there is an outlier: suggest which data point might be an outlier and recommend what can be done with it from a statistical point of view

c) Comment further on your second charter further once you have eliminated the outlier data point

d) Suggest an alternative approach to dealing with this outlier

© Duncan Williamson

March 2011

There is a solution to the case study for anyone who is interested and it is available for just GBP10 or US$16: the solution is fully worked and it includes a 14 minute video that clearly sets out the two main spreadsheeting techniques needed for the case. Send me an email to sales@excelmaster.co.uk with a brief note to tell me you want to buy the solution and I will send you full instructions.

Duncan Williamson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: