Comparative Median Income for Four-Person Families

Median Income for 4-person Families by State
Calendar year	2003	2004	2005	Average	% Change
United States	$62,732	$63,278	$62,228	$62,746	0.81%
Alabama	$53,754	$54,594	$51,451	$53,266	4.48%
Alaska	$69,868	$71,395	$66,874	$69,379	4.48%
Arizona	$56,857	$56,067	$55,663	$56,196	2.15%
Arkansas	$49,551	$47,838	$44,537	$47,309	11.26%
California	$65,766	$63,761	$63,206	$64,244	4.05%
Colorado	$68,089	$67,634	$66,624	$67,449	2.20%
Connecticut	$81,891	$82,517	$82,702	$82,370	-0.98%
Delaware	$69,469	$73,301	$69,360	$70,710	0.16%
District of Columbia	$55,692	$61,799	$63,406	$60,299	-12.17%
Florida	$57,473	$56,824	$55,351	$56,549	3.83%
Georgia	$60,676	$59,497	$59,489	$59,887	2.00%
Hawaii	$67,564	$66,014	$65,872	$66,483	2.57%
Idaho	$54,279	$51,098	$53,722	$53,033	1.04%
Illinois	$69,168	$66,507	$68,117	$67,931	1.54%
Indiana	$63,022	$63,573	$62,079	$62,891	1.52%
Iowa	$61,238	$61,656	$57,921	$60,272	5.73%
Kansas	$61,926	$61,868	$56,784	$60,193	9.06%
Kentucky	$54,030	$54,319	$51,249	$53,199	5.43%
Louisiana	$52,299	$51,234	$47,363	$50,299	10.42%
Maine	$58,802	$58,425	$56,186	$57,804	4.66%
Maryland	$77,938	$82,879	$77,562	$79,460	0.48%
Massachusetts	$78,312	$80,247	$78,025	$78,861	0.37%
Michigan	$67,995	$68,337	$68,740	$68,357	-1.08%
Minnesota	$72,379	$72,635	$70,553	$71,856	2.59%
Mississippi	$47,847	$46,810	$46,331	$46,996	3.27%
Missouri	$59,764	$61,036	$61,173	$60,658	-2.30%
Montana	$51,791	$48,078	$46,142	$48,670	12.24%

Comparative Median Income for Four-Person Families

Terrence Lewis works for an employment agency and needs to provide information about salaries in different states for his clients. He has started a worksheet with data from the years 2003-2005. After following the directions below to complete the worksheet, the first page of your solution will be similar to that shown here.

a. Create the workbook. Spell check the worksheet and correct any misspelled words.

b. Edit the title in cell A1 by capitalizing the first letter of each word except the words for and by and by deleting the comma following Families. Merge and center the title across columns A through F. Increase the font size to 12, and bold and apply color of your choice to the title.

c. Center-align and underline the column headings in row 2. Adjust the width of column A to fully display the labels. Insert blank rows above and below row 1.

d. Right-align cells B5 through D56 and format cells as Accounting with zero decimal places.

e. Enter the heading Average in cell E4. Center and underline the title. Calculate the average income in cell E5 using the function =Average(B5:D3). Format the cell as Accounting with zero decimal places. Copy the formula to cells E6 through E56.

f. Next, you would like to calculate the percent of change from 2003 to 2005. Enter the heading %Change in cell F4. Center and underline the title. Enter the formula =(B5-D5)/D5 in cell F5. Format the cell as a percentage with two decimal places. Copy the formula to cells F6 through F56.

g. Add a column to the right of the % Change column, title this column +/-

h. Use an IF statement to output whether or not the value in the % Change column is Positive or Negative

i. Autofit columns B through F.

j. Add font and fill colors to the worksheet as you like. Locate the state with the highest positive % change and the state with the highest negative % change. Surround their entire rows with a thick box border.

k. To highlight the states with the largest percent change, add fill and font colors to the worksheet.

l. Enter your name and the current date on separate rows just below the last lines. Format the date to day, month, year (14-Mar-01) date format.

m. Move to cell A1. Save and replace the workbook as Family Income. Preview and print the worksheet.

n. Print the worksheet again with formulas using landscape orientation.