CSCI-A110 Lab 9
Spreadsheets II: Charting

Due date: 2011 Apr 19
50 points

This is version 2.2.1, revised 2011 Apr 7 by specifying the due date.

URL: The latest version of this document can be found online at http://mypage.iu.edu/~gdweber/csci/a110/lab/09-spread2.html

Lab Objective

Be able to use a spreadsheet to create graphical plots, or charts, of data.

Instructions

Recommended spreadsheet programs for this assignment include Microsoft Office Excel, Gnumeric, and Openoffice.org. You may use another spreadsheet, provided that it is able to produce the charts and print them.

Enter the data shown in the three tables following into a spreadsheet, and produce three charts. Each chart must be of the type specified below, and must have an appropriate title, legend, x axis label, and y axis label; except that for the first (population) chart, use either the legend, x axis label, or y axis label to identify the country of each bar, and the other two (of legend, x axis label, y axis label) are not needed.

Chart Types:

screenshot of a bar chart screenshot of a line chart screenshot of a scatter plot

  1. For the population data, the chart must be a column chart or a bar chart showing vertical or horizontal bars whose lengths are proportional to the numbers.
    • Do not turn in a pie chart. Although pie charts are often used for this type of problem, they can be misleading, because humans are not able to compare areas or angles as accurately as lengths.
    • Each bar in the chart must be identified either in the legend, the x axis labels, or the y axis labels. That is, the chart must make clear which country is on which bar.
  2. For the rainfall data, the chart must be a line chart, with one curve for each region. The vertical (y) axis should be the amount of rainfall, and the horizontal (x) axis should be time.
  3. For the School Spending and Performance data, the chart must be a scatter or "X-Y" plot. The y axis should be the average total SAT score, and the x axis should be the average expenditure per pupil.

Hints

  1. To add axis titles, in Microsoft Office Excel 2007, click on the chart. A "Layout" tab will appear at the top of the window (along with two other tabs that are just for charts); click on it, then on "axis titles."
  2. If you're using colors to visually distinguish between different lines, and printing in black and white, make sure that the colors are visually distinct in black and white tool.

Data

Table 1.

Population. Source: Wikipedia, 4/1/2007.
CountryPopulation
People's Republic of China 1,317,000,000
India1,126,000,000
United States 301,600,000
Brazil 186,250,000
Russia 141,520,000
Mexico 109,200,000
Egypt 79,950,000
South Korea 49,024,737
South Africa46,440,000
Poland 38,132,277
Australia20,810,000
Netherlands16,365,000

Table 2.

Rainfall in two regions. Source: Imagination.
Month: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Region Q 16.5 23.2 33.9 58.7 69.1 42.3 30.4 28.7 34.5 42.8 38.7 21.3
Region R 27.8 34.5 68.7 23.4 32.4 16.5 43.5 28.7 37.8 50.9 36.8 31.9

Table 3.

School Spending and Performance. Expenditure per pupil and average total SAT score (verbal + quantitative), for a selection of states in 1994-1995. Source: UCLA Department of Statistics.
Expenditure Score
4,5861015
5,222893
5,327896
5,718889
5,826882
5,906937
6,078889
6,1361048
6,1601001
6,162975
6,436947
6,750901
6,9301073
6,9941033
7,287907
9,623892

What to Turn in

Print the charts in the spreadsheet (or "workbook") and turn in the hardcopy. If you wish to print the entire spreadsheet, including the data (i.e., tables of numbers), that is okay too.

Grading Criteria

ChartCorrect chart type Correct data series Title Legend X axis label Y axis label Total
Population(bar/column) 5 5 2 (identifies countries) 2 14
Rainfall(line) 5 5 2 2 2 2 18
School(scatter/x-y) 5 5 2 2 2 2 18
TOTAL 50