2010 Population (US Census 2010):
Cause of Death
Diseases of heart
Chronic lower respiratory diseases
Accidents (unintentional injuries)
Nephritis, nephrotic syndrome and nephrosis
Influenza and pneumonia
Chronic liver disease and cirrhosis
Essential hypertension and hypertensive renal disease
All other causes
The National Center f
data from 2010 (
g.) (2 points)
Most likely male cause:
Most likely black cause:
h.) (2 points)
causes is most likely t
The National Center for Health Statistics tracks health statistics; in this problem you will look at mortality
data from 2010 (National Vital Statistics Report, Vol. 61, No. 4, May 8, 2013).
a.) (5 points) The table (A3:F21) gives you the top 15 leading causes of death and actual number of deaths
in the U.S. in 2010. In D6:D15 compute the part-to-whole ratio that compares deaths due to each of these
causes to the number of deaths from all causes. Format as a decimal to 4 places.
b.) (3 points) In E6:E15 rewrite these decimal values as percentages with one decimal place accuracy. Just
link these cells to those in column D with a simple formula, then format appropriately.
c.) (6 points) In F6:F15 compute the crude death rate per 100,000 people using the population given. Your
formula must somehow account for the "per 100,000" unit. Format these values to one decimal place.
d.) (3 points) In columns G & H the CDC has determined the part-to-part ratio of Male deaths to Female
deaths for each cause of death. Note the part-to-part ratio of 1.4 to 1 sums to a total of 2.4 given in column
I. Fill a formula down in I6:I20 which adds the parts in the corresponding cells of columns G & H.
e) (5 points) Then in J5:J20 compute the actual number of deaths for each cause for Males using the part
whole ratio (columns G & H) together with the actual number of deaths listed in column C.
f) (4 points) In K5:K20 similarly compute the actual number of deaths for each cause for Females. Format all
values in columns J & K to the nearest whole number.
g.) (2 points) Which of the top 15 causes is most likely to have a male death versus a female death? Your
answer should be one of the numbers 1–15 from column A.
h.) (2 points) Cells L5:L20 list the ratios of Black deaths to White deaths for each cause. Which of the top 15
causes is most likely to have a black death versus a white death? Your answer should be one of the numbers
1–15 from column A.
i.) (10 points) Create a nicely labeled (and titled!) pie chart showing the distribution of totals (column C
the top 5 causes of deaths, with a sixth group labeled All Other that lumps together the remaining number of
deaths. Label the pie pieces with their corresponding percentages of the total. (To build this pie chart, first
copy the first five rows of the table in A4:E10 into the space beginning in row 33 of columns A-E to the left of
this box. Then add a sixth row to take care of the All Other value. Use this new table to build the pie chart
ok at mortality
number of deaths
e to each of these
ce accuracy. Just
ation given. Your
aths to Female
.4 given in column
s using the part-to-
le death? Your
hich of the top 15
ne of the numbers
tals (column C) for
maining number of
his pie chart, first
to the left of
ld the pie chart
Purchase answer to see full attachment