Life Expectancy and Years of Potential Life Lost (YPLL) Calculation
Life expectancy is the average number of years of life remaining to a person at a particular age and is often used as a summary measure of the health status of a population. The most commonly used life expectancy measure is life expectancy at birth, the number of years a person born in a given year is expected to live. Calculation of life expectancy requires birth, death, and population data. It is recommended that there be at least 700 deaths in a given geographic area/race/sex group when calculating life expectancy. If there are fewer than 700 deaths, multiple years of data may be combined to give life expectancy for a range of years. Life expectancy does not change much from year to year and random variation can cause life expectancy to decrease slightly on occasion, therefore the overall trend is key.
In the following life expectancy Excel workbook, there are separate worksheets for all races and both sexes, males, females, whites, blacks, white males, white females, black males, and black females. The k-values in gray in the Qx column (column F) differ for each race and sex as does the value of s (cell D129), therefore the appropriate worksheet must be used for each race/sex combination. These values change annually and are available in the Methodology section of the Technical Notes of the annual United States Life Tables publications.
In the worksheets, input the appropriate age-specific number of deaths (column B) and population (column E) for the geographic area, race, and/or sex of interest in the gray cells. Note that cell B85 is the number of deaths of those aged 85 and over. The worksheet takes into account deaths of unknown age, so enter those in B121. In cells D124-D127, enter the geographic area/race/sex-specific numbers of infant deaths this year, infant deaths born last year, births this year, and births last year, respectively. The resulting life expectancy will appear in the red cell.
If single year of age population data are not available for the geographic area of interest, an abridged life table may be used. The abridged life table uses deaths and population in 5-year age groups. Unlike the complete life table, it does not require separate factors for each race and sex, nor does it take into account unknown age, births, or infant deaths. It does, however, require at least one death in each age group and at least 700 total deaths are recommended for accuracy. If there are fewer than 700 deaths or there are no deaths in at least one age group, multiple years of data may be combined to give life expectancy for a range of years. In the following abridged life expectancy Excel worksheet, input the appropriate age-specific number of deaths (column B) and population (column C) for the geographic area, race, and/or sex of interest in the gray cells. The resulting life expectancy will appear in the red cell.
In the following life expectancy Excel workbook, there are separate worksheets for all races and both sexes, males, females, whites, blacks, white males, white females, black males, and black females. The k-values in gray in the Qx column (column F) differ for each race and sex as does the value of s (cell D129), therefore the appropriate worksheet must be used for each race/sex combination. These values change annually and are available in the Methodology section of the Technical Notes of the annual United States Life Tables publications.
In the worksheets, input the appropriate age-specific number of deaths (column B) and population (column E) for the geographic area, race, and/or sex of interest in the gray cells. Note that cell B85 is the number of deaths of those aged 85 and over. The worksheet takes into account deaths of unknown age, so enter those in B121. In cells D124-D127, enter the geographic area/race/sex-specific numbers of infant deaths this year, infant deaths born last year, births this year, and births last year, respectively. The resulting life expectancy will appear in the red cell.
If single year of age population data are not available for the geographic area of interest, an abridged life table may be used. The abridged life table uses deaths and population in 5-year age groups. Unlike the complete life table, it does not require separate factors for each race and sex, nor does it take into account unknown age, births, or infant deaths. It does, however, require at least one death in each age group and at least 700 total deaths are recommended for accuracy. If there are fewer than 700 deaths or there are no deaths in at least one age group, multiple years of data may be combined to give life expectancy for a range of years. In the following abridged life expectancy Excel worksheet, input the appropriate age-specific number of deaths (column B) and population (column C) for the geographic area, race, and/or sex of interest in the gray cells. The resulting life expectancy will appear in the red cell.
Years of potential life lost (YPLL) is a measure of the number of years not lived by each individual who died before reaching a predetermined age, usually 65 or 75. (NCHS switched to YPLL before 75 in 1996 and CHS switched in 2000.) This measure weights deaths at younger ages more heavily than deaths at older ages; the younger the age at death, the greater the number of years of potential life lost. The YPLL for a population is computed as the sum of all the individual YPLL for individuals who died during a specific time period. In the following YPLL Excel worksheets, enter the appropriate age-specific numbers of deaths and the population under age 65 or 75 for the year, geographic area, race, sex, and/or cause of death of interest in the gray cells and the resulting YPLL rate will appear in the red cell.
- Crude Rates
- Age- and Sex-Specific Rates
- Age-Adjusted Rates
- Age-adjustment Worksheet based on the 2000 Standard Population
Clicking this link will take you to an Excel spreadsheet.
