Additional Questions Retrieve the data set EA1.3.3 Governors…
Additional Questions Retrieve the data set...
Retrieve the data set “EA1.3.3 Governors Salaries.xls” from the CD or website.
SSAc01.indd 402 6/19/06 7:54:08 PM
Choose appropriate classes and use Excel to create a histogram for the Governors’
Explain why you picked the classes you did.
Explain what your histogram shows.
Create, by hand, a stemplot of the “points scored” from the “EA1.3.2 Super
Bowl.xls” data file.
How is this graph different from the histogram for the same variable you
Activity 1.3: SATs and the Super Bowl 403
SSAc01.indd 403 6/19/06 7:54:08 PM
404 Excel Activities
Which plot gives a better sketch of the data and why? What information from the table is not reflected in your graphs?
In this activity, you grouped data to create histograms by hand and using Excel.
With Excel, you created histograms in two different ways: as a bar graph using the
frequencies you computed and then adjusting the width of the bars so that there
were no gaps in between them, and as a histogram using the histogram function
in Excel, in which frequencies are computed automatically.
SSAc01.indd 404 6In this activity, you will use scatterplots to investigate the relationship between
the governor’s salary and the average per capita income in the state. You
will also analyze differences between scatterplots and other types of graphs.
The following table gives the average per capita income and the governor’s
salary in 2003 for each state in a group of nine northeastern states.
State Per Capita Income 2003 Governor’s Salary 2003
Connecticut 43,173 150,000
Maine 28,831 70,000
Massachusetts 39,815 135,000
New Hampshire 34,702 100,690
New Jersey 40,427 157,000
New York 36,574 179,000
Pennsylvania 31,998 142,142
Rhode Island 31,916 105,000
Vermont 30,740 127,456
SSAc02.indd 412 6/19/06 7:54:28 PM
Explain how you could use a bar graph to represent and interpret one or
more aspects of the data. Which aspects of the data would the bar graph
Would a histogram help you interpret the data, or an aspect of the data?
Which type of graph would help you answer the question: Is there any relationship
between per capita income in a state and the governor’s salary?
Activity 2.2: State Governors’ Salaries and Per Capita Income 413
SSAc02.indd 413 6/19/06 7:54:28 PM
414 Excel Activities
Create a scatterplot of the data using the two quantitative variables, “per
capita income” and “governor’s salary.”
Which variable did you use on the x-axis? Why?
Which variable did you use on the y-axis? Why?
SSAc02.indd 414 6/19/06 7:54:29 PM
Explain what your graph shows about the relationship of the two variables.
To analyze this relationship further you will create a scatterplot using data on
all 50 states. To do so, you will first create a two-column table that records the
governor’s salary and the average per capita income for each state.
Instructions to Use Excel to Create a
Table from Other Tables
Retrieve the file “EA2.2.1 Per Capita Income.xls” from the CD or website.
This file contains a list of all states and the per capita personal income for
each for 2003. (Source: Bureau of Economic Analysis, www.bea.gov.)
Open the file “EA2.2.2 Governors Salaries.xls” from the CD or website, and
then follow instructions in part c to copy and paste the list of Governors’
Salaries into the worksheet “EA2.2.1 Per Capita Income.xls.”
First, ensure that both files contain all the states listed in the same order.
Then put the cursor at the top of column B, “Governors’ Salaries,” and click
to highlight the entire column. Select Edit from the menu bar and then
Copy to copy column.
Return to the per capita income worksheet. Place the cursor at the top of
column C, and then select Edit and Paste to insert the governors’ salaries
into column C of the per capita worksheet. You should now have three columns
in this worksheet.
Activity 2.2: State Governors’ Salaries and Per Capita Income 415
SSAc02.indd 415 6/19/06 7:54:29 PM
416 Excel Activities
Create a scatterplot of this data set. You should change the scale of the horizontal
(or x) and vertical (or y) axes so the data are easier to read. (To change
the scale on an axis, point to the axis and either double-click or right-click
and select Format axis. Then on the Scale tab, enter the appropriate minimum
and maximum values for the axis.) What is a reasonable minimum
value of x to use? What about y?
Are there any trends or patterns to this data? Explain.
Are there any data points that appear to be “away from” the rest of the data? If
so, which one(s) and what makes them stand out?
SSAc02.indd 416 6/19/06 7:54:29 PM
The file “EA2.2.3 Children in Poverty.xls,” which you will find on the CD or
website, contains the table shown here. This table gives the percentage of children
younger than 18 who were living below the poverty level in the U.S. from
1976 to 2001. Use Excel to create a scatterplot for this data table and write a
paragraph describing the trends shown by your graph. Does this scatterplot
represent a function? Why or why not? (Source: Encyclopaedia Britannica
Almanac 2004, pages 828–829.)
Year Percentage of Children Year Percentage of Children
1976 16.0 1989 19.6
1977 16.2 1990 20.6
1978 15.9 1991 21.8
1979 16.4 1992 22.3
1980 18.3 1993 22.7
1981 20.0 1994 21.8
1982 21.9 1995 20.8
1983 22.3 1996 20.5
1984 21.5 1997 19.9
1985 20.7 1998 18.9
1986 20.5 1999 16.9
1987 20.3 2000 16.2
1988 19.5 2001 16.3
Activity 2.2: State Governors’ Salaries and Per Capita Income 417
SSAc02.indd 417 6/19/06 7:54:30 PM
418 Excel Activities
The table here (also available in the “EA2.2.4 US Farms.xls” file on the CD or
website) gives the total number of acres (in thousands) of land devoted to farming
in the U.S. and the farm population (in thousands) at ten-year increments
from 1900 to 1990. (Source: The New York Times Almanac 2004, page 302.)
Year Farm Acreage (in thousands) Farm Population (in thousands)
1900 841,202 29,835
1910 881,431 32,077
1920 958,677 31,974
1930 990,112 30,529
1940 1,065,114 30,547
1950 1,161,420 23,048
1960 1,176,946 15,635
1970 1,102,769 9,712
1980 1,039,000 6,051
1990 987,000 4,801
Create an appropriate scatterplot that you can use to analyze any trends
about the land used for farming throughout the 20th century. Describe
SSAc02.indd 418 6/19/06 7:54:30 PM
Create a second scatterplot and use it to describe the relationship between the
number of acres used for farming and the total farm population in the U.S.
In this activity, you considered the differences among a bar graph, a histogram,
and a scatterplot and the information conveyed. You learned to copy and paste in
Excel. You looked at trends in a sample compared to trends in a full data set and
looked for unusual observations. You also investigated trends over time and relationships
Activity 2.2: State Governors’ Salaries and Per Capita Income 419