Problem Set 3: Standardization of rates and spreadsheet skills

Objectives:

To calculate age-specific and crude death rates using a spreadsheet program.

To describe the age pattern of mortality and age distribution, and to understand how they each affect the crude death rate.

To perform and interpret results of an age standardization of mortality rates.

To learn basic spreadsheet skills of data entry and programming.

Note: This problem set involves a number of steps and will be more time consuming than most other problem sets. I recommend that you spread the work over a couple of weeks as we learn the skills in class so the concepts or the work does not overwhelm you.

I recommend that you complete steps 1 through 4 during the first week after the assignment has been introduced, and steps 7 and 8 in the second week. Steps 5 and 6 could be done either week, but must precede steps 7 and 8.

1) Data entry and basic calculations (20 points).

1. Obtain an electronic copy of the table above. Paste it into an Excel spreadsheet.
2. Insert a column for death rates among black women. Calculate the age specific mortality rates for black women.
3. Insert columns for proportion of the population.

• among black women
• among the overall population.
• Calculate the pertinent numbers to fill in those columns.

1. Insert a row for all age groups. Calculate:

• total number of black women in the United States.
• crude death rate among black women.

e. total number of persons (all ages, both sexes, all races) in the United States

2) Be sure to use consistency checks to make sure the proportions and rates you have calculated follow expected patterns and are internally consistent (see class notes on classification and on mortality rates).

3) Print out your spreadsheet and write the row labels (#s) and column labels (letters) (5 points)

4) Formulas (10 points) Hand write the formulas on the bottom of your spreadsheet.

1. In what cell of the spreadsheet can the CDR for black women be found, and what formula did you program to calculate it? (Use cell addresses to express your formula.)
2. In what cell is the mortality rate for black females aged 5-9 (use cell addresses). What is the value of the ASMR for that group?
3. What proportion of the overall U.S. population is aged 55-59. What formula did you use to calculate that figure?

5) Age pattern of mortality (15 points)

Use Excel to create a line graph showing mortality rates by age for black females. Include a title and labels for the x- and y-axes.

Print out the chart.

Does the age pattern of mortality correspond to what we have learned in class?

6) Age distributions (15 points)

1. Use Excel to create a line graph showing the proportion of the population by five-year age group for:

• black females
• The overall U.S. population (all races, both sexes)
1. How do the age distributions for the two groups differ?

7) Age standardization (20 points)

1. Use your spreadsheet to calculate the age-standardized crude death rate for black females, using the age distribution of the U.S. (all races, both sexes) as the standard population. Hint: There are two steps to the standardization calculation. You will need to add another column to make space for a calculation that is the first step of the standardization process.
2. Write out the formulas that you used to calculate the age-standardized crude death rate.

8) Interpretation of standardized and unstandardized rates (15 points)

1. Is the age-standardized crude death rate for black females higher or lower than the unstandardized crude death rate for black females?
2. Explain why or why not, with reference to the information on the age distribution and age pattern of mortality.

 Number of persons and number of deaths by five-year age group, black females, United States, 1997 Age group (years) Black females All racial groups, both sexes # of deaths 1000s of persons 1000s of persons <1 3786 295 3881 1-4 587 1151 15353 5-9 445 1550 19738 10-14 369 1444 19040 15-19 713 1457 19068 20-24 1004 1313 17512 25-34 3805 2827 39610 35-44 8302 2892 43998 45-54 11547 1956 33633 55-64 16379 1253 21813 65-74 26241 958 18499 75-84 30856 544 11706 85 and older 28348 207 3871 Total

Back to epidemiology syllabus.