Chapter 2 Working with Data 77
a. Enter the data from Table 2-8 into a
blank workbook and save the work-
book as Big Ten Graduation to the
Chapter02 folder.
b. Create two new columns displaying
the difference between white male
and white female graduation rates
and the ratio between white male and
white female graduation rates. How
do graduation rates compare?
c. Create two more columns calculating
the difference and ratio of the white
female graduation rate to the overall
rate from 1997 to 2000.
d. What do you observe from the data?
Does one university stand out from
the others?
e. Sort the data fi les in descending order
of the ratio of white male to white
female graduation rate. Create range
names for all of the columns in the
workbook.
f. Save your changes to the work-
book and write a summary of your
observations.
- Over 4,000 television viewers were
interviewed in 1984 to determine which
television ads were remembered for being
signifi cant and interesting. The level of
retained impressions were then compared
to the advertising budgets from each fi rm.
(Source: Wall Street Journal, 1984.)
a. Open the TV Ads workbook from the
Chapter02 folder and save it as TV
Ads Analysis.
b. Calculate the ratio of the retained
impressions per week to the advertising
budget.
c. Create range names for the three
columns in the worksheet.
d. Sort the list in descending order of
ratio values. Which fi rm showed the
greatest bang for the buck from their
advertising dollars? Print the sorted
data values.
e. Filter the data list, showing only
those fi rms with a higher-than-average
ratio of retained impressions to adver-
tising dollars. Print the fi ltered values.
f. Save your changes to your workbook
and then write a report summarizing
your observations.
- The Teacher.txt fi le contains the aver-
age public teacher pay and spending
on public schools per pupil in 1985 for
50 states and the District of Columbia
as reported by the National Education
Association.
a. Open the Teacher.txt fi le from the
Chapter02 data folder as a tab-
delimited text fi le. There are four
columns in the text fi le. The State col-
umn contains the abbreviations of the
50 states and the District of Columbia.
The Pay column contains the average
annual salary of public school teachers
in each state and district. The Spend
column contains the public school
spending per pupil for each state and
district. The Area column contains the
area in the country for each state or
district. Import all of these columns
except the Area column.
b. Create a new column calculating the
ratio of the Pay column to the Spend
column.
OSU 66 60 42 77 83
PSU 84 76 69 91 93
PU 67 66 48 84 80
WIS 77 65 50 79 64
