Chapter 2 Working with Data 75
3 Click the Offi ce button and then click Close.
4 Click the Offi ce button and then click Exit Excel.
Exercises
- Air quality data had been collected from
the Environmental Protection Agency
(EPA) and stored in an Excel workbook.
The workbook displays the number of
unhealthful days (heavy levels of pollution)
per year for 14 major U.S. cities in the year
1980 and then from 2000 through 2006.
Open this workbook and examine the data.
a. Open the Pollution workbook from
the Chapter02 folder and save it as
Pollution Report.
b. Create a new column named
AVER0006 that uses Excel’s average()
function to calculate the average
pollution days for each city from 2000
through 2006.
c. Create a new column named DIFF06
80 that calculates the difference
between the average number of pollu-
tion days from 2000 through 2006 and
the number of pollution days in 1980
for each of the 14 cities.
d. Sort the data in ascending order of the
DIFF80_06 column you just created.
Which cities showed an increase in
the number of pollution days? Which
cities showed a decrease? Which city
showed the greatest improvement in
terms of the decline in the number of
unhealthy days?
e. Create a new column that calculates
the ratio of unhealthy days between
the average from 2000 through 2006
and the value for 1980. Name the col-
umn RATIO06_80.
f. Format the values in the RATIO06_80
column as a percentage to two deci-
mal places.
g. Sort the data in ascending order of
the RATIO06_80 column. Which city
showed the greatest improvement in
the terms of the ratio of the 2006 aver-
age to the 1980 value?
h. Create range names for all of the col-
umns in your workbook.
i. Save your workbook and write a re-
port summarizing your observations.
Does the data prove any conclusions
you might have reached? What kind
of information might be missing from
this data set? Remember that you only
have one year’s worth of data from the
1980s versus seven years data from
2000 to 2006. In what way could the
average value from those seven years
not be comparable to a single year’s
value from 1980?
- Data on soft drink sales shown in
Table 2-7 have been saved in a text fi le.
The fi le has fi ve variables and ten cases.
The fi rst variable is the name of the soft
drink brand; the next three variables are
company sales in millions of 192-ounce
cases for the years 2000, 2001, and
2002. (Source: http://www.bevnet.com/
news/2002/03-01-2002-softdrink.asp,
Beverage Marketing Corporation.) The
fi nal column indicates the year of origin
for each brand.
