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.

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

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:
  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.

Back to home page for Prof. Miller


Last updated 11/25/02. Jane Miller.