78 Excel
c. Create range names for all of the col-
umns in the worksheet.
d. Sort the data in ascending order of the
Ratio column.
e. Filter the data values, showing only
those states or districts that have a
ratio value of less than 6.
f. Save your workbook as Teacher
Salary Analysis to the Chapter02
folder in Excel workbook format and
summarize your observations.- Working in groups in a high school
chemistry lab, students measured the
mass (grams) and volume (cubic cen-
timeters) of eight aluminum chunks.
Both the mass in grams and the volume
in cubic centimeters were measured
for each chunk. Analyze the data from
the lab.
a. Open the Aluminum workbook from
the Chapter02 folder and save it as
Aluminum Density Analysis.
b. Create a new column in the work-
sheet, computing the density of each
chunk (the ratio of mass to volume).
Apply a range name to the new
column.
c. Sort the data from the chunk with
the highest density to that with the
lowest.
d. Calculate the average density for all
chunks.
e. Is there an extreme value (an observa-
tion that stands out as being different
from the others)? Calculate the aver-
age density for all chunks aside from
the outlier. Print your results.
f. Which of the two averages gives the
best approximation of the density of
aluminum? Why?
g. Save your changes to the workbook
and summarize your results. - The Economy workbook has seven vari-
ables related to the US economy from
1947 to 1962.
a. Open the Economy workbook from
the Chapter02 data folder. The
Defl ator variable is a measure of the
infl ation of the dollar; arbitrarily set
to 100 for 1954. The GNP column
contains the Gross National Product
for each year (in millions). The
UnEmploy column contains the num-
ber unemployed in thousands, and
the Arm Force column has the num-
ber in the armed forces in thousands.
The Population column contains the
population in thousands. The Total
Emp contains the total employment
in thousands. Save the workbook as
Economy Data.
b. Create range names for each column
in the worksheet.
c. Notice that values in the Population
column increase each year. Use the
Sort command to fi nd out for which
other columns this is true.
d. There is an upward trend to the GNP,
although it does not increase each
year. Create a new column that cal-
culates the GNP per person for each
year. Name this new column GNPPOP
and create a range name for the values
it contains.
e. Save your changes to the workbook
and write a report summarizing your
observations.- An analyst has collected 2007 data in-
cluding salary and batting average for
major league players. Examine the data
that have been collected.
a. Open the Baseball workbook from the
Chapter02 folder and save it as Base-
ball Salary Analysis.
b. Create range names for all of the
columns in the workbook.
c. Sort the data values in descending
order of batting average.
d. Display only those players whose
career batting average is 0.310 or
greater. List these players.