Chapter 2 Working with Data 79e. Remove the fi lter, displaying all val-
ues in the workbook again.
f. Add a new column to the worksheet,
displaying the batting average divided
by the player’s salary and multiplied
by 1,000,000.
g. Sort the worksheet in descending or-
der of the new column you created.
Who are the top-ten players in terms
of batting average per dollar? Print
your results.
h. Examine the number of years played
in your sorted list. Where does it ap-
pear that most of the fi rst-year players
lie? What would account for that?
(Hint: What are some of the other fac-
tors besides batting average that may
account for a player’s high salary?)
i. Save your workbook and write a re-
port summarizing your observations.- An analyst has collected data on the
death rates for diabetes and infl uenza-
pneumonia for the year 2003. The data
have been saved in the Health work-
book. Your job is to examine the data
values from the workbook.
a. Open the Health workbook from the
Chapter02 folder and save it as Health
Report Analysis.
b. Sort the data by ascending order of
diabetes-related deaths. Then do a
sort on infl uenza-pneumonia related
deaths. Which states have the highest
and lowest values in those categories?
c. Use the AutoFilter to list the top-ten
states in each category. Print your fi l-
tered worksheet.
d. Turn off the fi lter and create a new
column calculating the ratio of the
diabetes-related death rate to the
pneumonia-related death rate in each
of the 50 states. Create a range name
for the new column.
e. Format the ratio values in the new
column as percentages to two decimal
places.
f. Sort the data in ascending order of the
new column. Which state or region
has the highest ratio of diabetes-
related deaths? African-Americans
have a much higher rate of diabetes
than whites. Discuss how this ex-
plains your observation of the state
or region with the highest rate of
diabetes-related death.
g. Create range names for the all of the
columns in your workbook.
h. Save your changes to the workbook
and write a summary of your
observations.- The Cars workbook contains data from
Consumer Reports.org®, February 1,
2008, on 275 different car models, as
described in the following table:
Table 2-9 Car Data
Field Description
Model ID Number from 1 to 275
Model Make and model
Type Type of vehicle
Price Price in dollars
HP Horsepower
Eng size Engine size in liters
Cyl Number of cylinders
Eng Type Type of engine
(continued)