Cemetery Data Analysis

CEMETERY DATA ANALYSIS USING
MS EXCEL

D. B. Hoagland

For this experiment you will collect dates of birth and death for persons buried in a cemetery. We will use MS Excel to estimate various parameters, such as mean life span for males and females, average life expectancy, mortality rates, and survivorship for our graveyard population. Some of these data will be represented graphically to facilitate our investigation. Inspect these graphs carefully and identify trends in the data. Can you pose any questions after close scrutiny of the data?

The following procedures will assist your analysis.

These instructions were written for use with MS Excel version 5.0. Load MS Excel by following the instructions on the screen. A blank worksheet should appear. Check in the Tools menu under Data Anaylsis. . . , to see if you have the function Histogram loaded into the program. If Histogram is not present, you must load it. To accomplish this feat, pull down the Tools menu and select Add-Ins. . . Choose the Analysis ToolPak. WARNING: MS EXCEL USES LOTS OF RAM, AND THIS OPTION USES EVEN MORE!

MS Excel basics (extremely basic)
1. Data input is controlled by using regular boxes or "cells", which have an address that contains a letter denoting a column and a number indicating a row. The width and height of cells are changeable.
2. To place information into a cell it must be highlighted. Use arrow keys or the mouse to move the cursor to a specific cell.
3. The arrow cursor can also be used to pull down a menu from the top of the worksheet.
4. Cell contents can be deleted by highlighting the cell and choosing delete from the menu or hitting the delete key
5. Choose File/Save often
Data entry and analysis.
1. Label five consecutive columns: Marker (optional), Sex, DOD (Date of Death), DOB (Date of Birth), and Age. Start ten rows from the top of the worksheet.
2. Enter data: type in the recorded data into the first three or four columns.
3. Calculate Age data
  1. Enter =(first DOD datum address)-(first DOB datum address). Example: =C12-D12
  2. Copy the formula and paste to all cells in the Age column
  3. Sort data by sex
    1. Highlight all data (exclude column labels)
    2. Choose Data/Sort from the menu
    3. Scroll to Sex column in the Sort By window
    4. Select Ascending order
    5. Click OK
  4. Description of composite data
    1. Label seven rows above the data with the following: Count, Mean Age, Max Age, Min DOB, Max DOB, Min DOD, Max DOD
    2. In the cell adjacent to Count, type =count (range of cells for Age, e.g., E10..E110), alternatively, highlight the range of cells with the cursor, hit return or enter
    3. In the cell adjacent to Mean Age, type =average (range of cells for Age, e.g., E10..E110), hit enter or return
    4. Adjacent to Max Age, type=max (range of cells for Age, e.g., E10..E110), hit enter or return
    5. Continue for Min DOB, Max DOB, Min DOD, and Max DOD
  5. Description of data by sex.
    1. Copy male data and female data to new ranges
    2. Complete above descriptive analysis for each sex
Calculating a Life Table
1. Label the next column following Age as Age Class
2. Type age groups (five year intervals) in the Age Class column in ascending order. Begin with 0, 1, 6, 11, 16 and end with two years older than the Max Age ( if the oldest person is 101, add 102 and 103)
3. Definitions of variables
  1. x = age interval or age class
  2. n_x = number of survivors at start of age interval X (raw field data)
  3. l_x = proportion of organisms surviving to start age interval x
  4. d_x = number or proportion dying during age interval x to x + 1
  5. q_x = rate of mortality during the age interval x to x + 1
  6. L_x = number of individuals alive on the average during the interval x to x + 1
  7. T_x = total years lived by individuals in the population
  8. e_x = mean expectation of life for individuals alive at the start of age interval x
4. Calculation of x and d_x
  1. Choose Tools/Data Analysis/Histogram
  2. Select Age Data column with the label in the Input Range window
  3. Select Age Class column with the label in the Bin Range window
  4. Select labels, output range, and check OK. The number of individuals that died in each age class is computed and placed in the frequency column adjacent to the bin column
  5. Relabel the bin column to x and the frequency column to d_x
  6. Label consecutive columns following d_x: l_x, 1000q_x, L_x, T_x, and e_x
5. Calculation of l_x
  1. The first l_x value (l₀) is equal to the sample size (n)
  2. The remaining l_x values are calculated as follows:
    1. The second l_x value (l₁) is equal to l₀ - d₀
    2. Enter this formula into the second cell in the l_x column, e.g., =V16-W16, where V16 is the cell address for l0 and W16 is the cell address for d0
    3. Copy this formula to the remaining cells in this column
  3. Calculation of 1000q_x
    1. Set format in this column to one decimal place
    2. Highlight all cells in the column
    3. Select Cells from Format menu
    4. Select Custom, type 0.0 in Code box
    5. Enter =1000*(d_x/l_x) in the first cell in this column, NOTE: USE CELL ADDRESSES FOR d_x AND l_x
    6. Copy formula to all remaining cells in this column
  4. Calculation of L_x
    1. Set format in this column to one decimal place
    2. Enter =0.5*(x₁-x₀)*(l₁+l₀), NOTE: USE CELL ADDRESSES FOR VARIABLES
    3. Copy formula to all remaining cells in this column
  5. Calculation of T_x
    1. Set column format to one decimal place
    2. Set column width to 12
    3. T_x is the cumulative sum of L_x values from the bottom of the column
    4. Type =sum(current cell address..$last cell column letter$last cell row number), e.g., =sum(X39..$X$39)
    5. Copy formula to the cells above
  6. Calculation of e_x
    1. Set column format to one decimal place
    2. Type =T_x/L_x, NOTE: USE CELL ADDRESSES FOR VARIABLES
    3. Copy formula to remaining cells
Graphing results
1. Plot Temporal Trends in data using Chart Wizard
  1. Select the Chart Wizard icon from the menu bar
  2. Create a space for the chart by placing the cursor in an area of the worksheet containing empty cells, depress the mouse button, and drag to desired chart size. A dialogue box should appear entitled "Chart Wizard - Step 1 of 5" with the Range box displaying a cell(s) address
  3. Select Year of Death (from the raw data) for Temporal Trends
  4. Depress the Command key, and select the Age of Death data (from raw data), hit Next button
  5. Choose Line Chart type, hit Next button
  6. Choose an appropriate Line Chart (#2), hit Next button
  7. Choose Data Series in Columns, Use First (1) Column for Category (X) Axis Labels, Use First (1) Row for Legend Text, hit Next button
  8. Add a Legend - Yes, Type in an appropriate Chart Title, and Axes Titles (X=Year of Death, Y=Age of Death), hit Finish button
  9. Format axes by doubling clicking on the axis and adjust accordingly
2. Plot Mortality, Mortality Rate vs. Age, Survivorship, and Life Expectancy as follows: Choose Age data (from Life Table data) as the X axis and the following data as Y variables:
  1. d_x for Mortality
  2. 1000q_x for Mortality Rate vs. Age
  3. l_x for Survivorship
  4. e_x for Life Expectancy

Any comments or suggestions? If so, please send them to Buzz Hoagland (bhoagland@wsc.mass.edu)

Last updated

Return to the Biology Department Homepage

Return to the WSC Homepage