Key College Publishing | Data Matters Resource Center

To do this, you will enter the median incomes and the consumer price index (CPI) values and create a new column of values that has the median incomes in real dollars.

Consumer Price Index
Year	CPI	Year	CPI
1967	33.4	1984	103.9
1968	34.8	1985	107.6
1969	36.7	1986	109.6
1970	38.8	1987	113.6
1971	40.5	1988	118.3
1972	41.8	1989	124.0
1973	44.4	1990	130.7
1974	49.3	1991	136.2
1975	53.8	1992	140.3
1976	56.9	1993	144.5
1977	60.6	1994	148.2
1978	65.2	1995	152.4
1979	72.6	1996	156.9
1980	82.4	1997	160.5
1981	90.9	1998	163.0
1982	96.5	1999	166.6
1983	99.6	2000	172.2
		2001	177.1

Source: U.S. Bureau of Labor Statistics

Median and Mean U.S. Household Incomes (in thousands of dollars; not adjusted for inflation)
Year	Median Income	Year	Median Income
1967	$ 7	1984	$22
1968	$ 8	1985	$24
1969	$ 8	1986	$25
1970	$ 9	1987	$26
1971	$ 9	1988	$27
1972	$10	1989	$29
1973	$11	1990	$30
1974	$11	1991	$30
1975	$12	1992	$31
1976	$13	1993	$31
1977	$14	1994	$32
1978	$15	1995	$34
1979	$16	1996	$35
1980	$18	1997	$37
1981	$19	1998	$39
1982	$20	1999	$41
1983	$21	2000	$42

Source: U.S. Census Bureau

Label column A Income, column B CPI, and column C RealIncome. Enter the median incomes in column A and the CPI values in B.

Pick a year to adjust to and note the CPI value of that year. These instructions will proceed as if you were adjusting to 2001. Replace 177.1, 2001’s CPI value, with the value you need for the year you are adjusting to.

In cell C2, enter the formula =A2*177.1/B2 . Drag that equation as far down as you need.

To report the real incomes, select them, right-click on the selection, and select Copy.

Data Matters with Excel®

Activity 5.3