Excel – Data Analysis – DUE TONIGHT, IN 6 HOURS

Excel HW Assignment with 4 main questions.  The instruction is provided on the first attachment and the data on the second attachment.

CS171G

Lab 11 – Data Analysis

* There is NOT a Module to read for this lab.

Definitions:

workbook: an Excel file. All data, formulas and charts for this assignment go in one
workbook named Unemployment.xlsx.

worksheet: a page in an Excel file. Worksheets are indicated by Tabs at the bottom of

the screen. By default, a workbook contains 3 worksheets labeled Sheet 1,

Sheet 2, and Sheet 3.

What to Submit:

 Submit the completed Excel file Unemployment.xlsx.

Download the file Unemployment.xlsx from Canvas. Sheet 1 of the Excel file contains the

unemployment information for the U.S. from 2003 to 2012.

1. Plot a comparative graph of unemployed men and women of 16 years and over.

Suggested steps:

1.1 Create a new worksheet named 16+, by gender.

1.2 In column A, type the labels Year, Men, and Women. Copy the years from Sheet 1 (row

7, columns B through K) and paste them into row 1 of the 16+, by gender worksheet.

Copy the unemployment values for men (Sheet 1, row 8, columns B through K) and paste

them into row 2. Copy the unemployment values for women (Sheet 1, row 8, columns V

through AE) and paste them into row 3. At this point, your 16+, by gender worksheet

should look like the following table.

1.3 Select the table (columns A through K, rows 1 through 3), click the Insert tab, and in the

Charts group, select Scatter, then Scatter with Smooth Lines and Markers.

1.4 Add a Primary Horizontal Axis Title, Title Below Axis, and enter the word Year. Right

click on the numbers along the bottom of the chart, then select Format Axis. Set the

minimum value to Fixed, 2003; the maximum value to Fixed, 2012; and the major unit to

Fixed, 1.0.

1.5 Similarly, modify the vertical axis such that the minimum value is 2000 and the maximum

value is 10000. Add a Primary Vertical Axis Title, Rotated Title and enter the words

Unemployed [thousands].

1.6 Add a chart title, Unemployment, 16 years and over, by gender.

1.7 Save the workbook. (It’s a good idea to save your work often. )

The chart should look like the figure shown below.

What can you infer from the chart? Enter your answer in row 5 of the worksheet.

2. Plot a comparative graph of unemployed men of 16 years and over with different marital

status.

Suggested steps:

2.1 Create a new worksheet named 16+, men, by marital status.

2.2 Copy the numbers of unemployed men, 16 years and over (Sheet 1, rows 9 through 11,

columns A through K) and paste them into the new worksheet to form the following table.

2.3 Select the table (columns A through K, rows 1 through 4). Using the same steps as the

first chart you created, plot the chart, using Scatter with Smooth Lines and Markers

2.4 Modify the axes, the axis titles, and chart title correspondingly.

The chart should look like the figure shown on the next page. What can you infer from the chart?

Enter your answer in row 6 of the worksheet.

3. Create a new worksheet named Total, 25 years and over. Your goal is to compute the

unemployment rate for all people, 25 years and over, for each year in the 10-year period.
The data you need can be found in row 33 of Sheet 1.

Enter formulas to calculate the total Unemployed [thousands] and the Unemployment rates
for each year. The calculations for 2003 are shown in the following example.

Unemployed [thousands] of 2003 = 3,368 + 2,660

Unemployment rate of 2003 = (3,368 + 2,660) / (3,368 / 5 + 2,660 / 4.6) = 4.82

The table should be similar to the following figure. Columns B through E should have data for

all years. Columns F and G should have formulas to calculate the total Unemployed
[thousands] and the Unemployment rates for all years.

What year had the highest unemployment rate, according to your calculations? Enter your
answer in row 15 of the worksheet.

4. Create a new worksheet named 25+, by race. Repeat the unemployment rate calculations
for White (row 38 in Sheet 1), Black (row 43), and Asian (row 48) people, ages 25+.

The results have the following format:

Once you have all the calculations done, plot a chart similar to the figure shown below.

What can you infer from the chart? Enter your answer below the table in your worksheet.

 Submit the completed Excel file Unemployment.xlsx. You should have a total of 5

worksheets, including data, formulas, and charts as described above.

Sheet1

0

0

2003 2004 2005 2006 2007 2008 2009 20

2011 2012 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012

years and over

6

3

6

4

1

8

4

7

6

4

5.1 4.6

5.4

7

2

8

1

2

2.8

5.6

5

1,352

6

6.3 5.6

5.3

12.8

9.4

2

9

6.1 5.9 5.4 4.9 5 5.9

1

11 10.5

8.6

11

2

2

8.7

8.5 12 12.8

1

5.6 5

4

5.5 9.4 9.6 8.3

9

2,274

2

4.7 4.4 4 4 4.9 7.3 7.7

7

Married, spouse present

9

6

3.5 2.9 2.5

2.4 3.1

6.3 5.3 4.6

3.5 3.3 3

3.5 5.4 5.6 5.3 5.1

Widowed, divorced, or separated

466

421

5.9 5 4.7 4.9 6.5

10.3 8.8

5.6 5.5 4.9 4.7 4.9 5.6 8.8

9.2 8.3

Never married

9.7 9.1 8.2 7.5

15

12.2

7.4 7.1 6.8

6.3 7.3 10.5 11 10.5 9.9

891

949

11.1 11 9.5 9.1

15

868

839

9.5 8.4 7.5

12.4

12.8

Married, spouse present

200

5.9 5.6 5.1 4.7

5.9 10.3 11.6 11.1 8.4

200 5.5 5.3 5.2 4.4 4.3 4.6 6.6 7.9 7.8 6.8

Widowed, divorced, or separated

92 135

177 9.8 8.9 9.5 8.3 7.5

16

166 155 135

257

252 7.8 7.8 7.3 6.4 5.7 7.7

11.8 10.9

Never married

548 502

6

903

19

15 17.5

540

499

709 830

15.2

12.5

19.3 17.5

119 160

6.2 4.5 4 3 3.1 4.1 7.9 7.8 6.8 5.8

118 95 110

5.7 4.3 3.9 3.1 3.4 3.7 6.6 7.1 7.3 6.1

Married, spouse present

64 61 49 54 78

135

5.3 2.9 2.7 2.2 2.2 3.1 6.6 6.5 5.2 4.4 90 62 62 55 61 64

124

111 5.2 3.4 3.3 2.9 3.1 3.1 5.6 6.1 6.2 4.8

Widowed, divorced, or separated 13 14 11 11 9 17 26 20 20 25 5.4 5.8 3.6 3.5 3 5.4 8.2 6.6 6.4 7.2 28 24 23 12 12 21 30 40 42 37 6.8 6.3 5.5 2.7 2.7 4.4 6.2 8.7 9 6.3
Never married 80 75 68 49 56 65

8.5 7.8 7.2 4.9 5.2 6.2

10.9 8.5 44 39 32 29 37 39 70 73 78 86 6.2 5.6 4.5 3.8 4.4 5.2 9.4 8.6 9.2 8.9

7.2 6.5 5.4 4.8 5.3

12.5

11.2 9.9 631

544

8.4 7.6 6.9 5.9 6.1 7.7 11.5

11.8 10.9

Married, spouse present

201 247

498 5.1 4.4 3.6 3 3.5 5.2 9.5 9.6 8.6 6.9

177

422 421

7.2 6.2 5.4 4.5 4.7 6.3 9.8 10 10 8.6

Widowed, divorced, or separated 105 85 69 73 85 125

166 7.4 5.8 4.5 4.2 5.1 7.4 13.3

10.5 9.3

130

97 110

218 239 217

8.2 7.9 6.2 5.5 6 6.9 11.1 11.8 10.7 11

Never married

394

10.5 10.3 8.7 7.8 8.3 11.5 17.3

239 206

408

10.5 9.6 9.8 8.2 8.3

14

15.1 13.8

5 4.4 3.8 3.5 3.6 4.8 8.8 8.9 7.9 6.8

4.6 4.4 4.2 3.7 3.6 4.4 6.9 7.4 7.3 6.8

Married, spouse present

3.7 3.1 2.7 2.4 2.5 3.3 6.5 6.6 5.7 4.9

3.6 3.3 3.1 2.7 2.7 3.4 5.3 5.7 5.4 5.1

Widowed, divorced, or separated

538

7.3 6.2 5.5 5.1 5.1 7 12.5 12.6 10.9 9.2

730

683 800

5.9 5.7 5.3 4.8 4.9 5.7 8.9 9.4 9.5 8.6

Never married

8.3 8 6.6 6.1 6.3 8 13.3 13.4 12.3 10.6

599 556

1,227

6.3 6.4 5.7 5.2 6.2 9.5 10.2 10 9.4

4.5 3.9 3.4 3.1 3.3 4.3 8.1 8.2 7.1 6.1

4 3.8 3.6 3.3 3.3 4 6.4 6.8 6.5 6.1

Married, spouse present

3.4 2.8 2.5 2.2 2.3 3 6.1 6.2 5.3 4.5 969

3.3 3.1 2.9 2.6 2.5 3.3 5.2 5.5 5.1 4.9

Widowed, divorced, or separated

1,007

871

6.8 5.7 4.9 4.6 4.8 6.5

12.1 10.2 8.6

563 519

947 1,006

5.4 5.3 4.8 4.5 4.7 5.4 8.6 9.1 9 8.2

Never married

670 567 555 587

7.4 7 5.7 5.3 5.5 7.1 12 12 10.6 9.4

373

5 4.6 4.9 4.5 4.3 4.9 8 8.4 7.8 7.7

457 621

1,102

8.8 8.4 7.6 7 6.6 8.9

15.2 12.3 589 589 568 505 453 566 809 920

7.9 7.9 7.5 6.5 5.8 7.1 10.1 11.5

10.6

Married, spouse present

191

156 149 203 358 389

292 5.9 5.5 5 4.5 4.1 5.7 10.2 11.2 11.1 8.3

141

113 113 127

215 207

5.3 5.2 4.9 4.2 4.1 4.5 6.5 7.8 7.6 6.6

Widowed, divorced, or separated

113 102 85

228 208 167 9.7 8.8 9.2 8.2 7.3 10.3

17.5 15.7 13

160

168 239 239

7.6 7.7 7.2 6.1 5.5 7.5

10.6 11.5 10.7

Never married

253 223

496 424

11.1 10.6 10.5 13.4

20.9 17.9 275

275 247

271

466 490 448 11.2 11.1 10.4 9.2 7.7 9.3

14.2

157

102 80 92 128 253 249 217 198 5.4 3.9 3.3 2.4 2.7 3.7 7.2 7 6 5

93 102 75 87 101

197 209

5.2 3.7 3.8 2.7 3 3.3 5.9 6.5 6.8 5.4

Married, spouse present

63 61 48 53 78

162 132 121 5.3 2.9 2.7 2.1 2.2 3.1 6.5 6.4 5.1 4.4 85 55 60 53 59 61 113 120 127 108 5 3.1 3.3 2.8 3 2.9 5.6 6 6.1 4.7

Widowed, divorced, or separated 13 13 11 11 9 16 26 19 19 24 5.4 5.7 3.6 3.4 2.8 5.4 8.1 6.5 6.2 7 28 21 23 10 12 21 29 39 41 35 6.9 5.9 5.5 2.3 2.7 4.4 6.2 8.6 9 6.1
Never married 35 41 30 21 30 34 65 67 65 53 5.8 6.6 5 3.1 4 4.7 9.2 9.2 9 6.4 18 17 19 13 17 19 34 38 41 44 4.3 4.2 4.5 2.7 3.1 4 6.7 6.6 7.3 6.9

536

401

672

5.9 5.1 4.1 3.8 4.2 6.1 10.9 10.8 9.5 8.1

408

448 737

7.2 6.6 5.8 4.9 5.1 6.2 9.9 10.7 10.2 9.5

Married, spouse present 284 253 214 189 234

648

476 4.9 4.2 3.5 3 3.5 5 9.4 9.5 8.6 6.9 231 202

166 234

367 6.8 5.9 5 4.2 4.3 6 9.5 9.8 9.6 8.4

Widowed, divorced, or separated 96 75 63 67 77 117 201 201 163 153 7.2 5.5 4.4 4.1 4.9 7.3 12.7 12.1 10.2 8.9 118 121 98 91

118 201 222 203 222 7.9 7.8 6.1 5.3 5.7 6.8 10.7 11.4 10.5 10.5

Never married 156 149 124 131 144

377

7.7 7.4 5.7 5.7 5.8 8.1 13.8 13.3 11.4 10.6 90 84 99 85 89 96 161 204 200 216 7.7 6.8 7.7 6.4 6.1 6.1 9.9 12 11.4 10.8

																																															HOUSEHOLD DATA	Source: U.S. Bureau of Labor Statistics
ANNUAL AVERAGES
Unemployed persons by marital status, race, Hispanic or Latino ethnicity, age, and sex
Marital status, race, Hispanic	Men	Women
or Latino ethnicity, and age	Unemployed [thousands]	Unemployment rates [%]	Unemployed [thousands]	Unemployment rates [%]
							20								3					200								4				200											5				200				6				200						7				200			8				200						9								201				20									11				20							12				10
Total,		16	4,		90	4,4	56	4,0	59	3,			75	3,882	5,033	8,	4			53	8,		62	7,	68	6,	77									6.3											5.6													5.1							4.6									4.7																6.1					10.3										10.5								9.4								8.2	3,		86	3,	69	3,531	3,		24	3,					19	3,	8	91	5,811	6,1	99	6,0		63	5,		73															5.7															5.4												4.5					8.1										8.6				8.5								7.9
									Married, spouse present	1,751	1,		466	1,		28	1,		14	1,	206	1,590	3,1			15	3,					13	2,		67		2,274						3.8															3.1					2.8				2.4					2.5							3.4												6.6													6.8								5.8																	4.9		1,		35	1,2		44	1,	168	1,0	42	1,0		49	1,3	25	2,057	2,		1	60	2,031	1,915						3.7									3.5													3.3							2.9								3.6													5.5													5.9															5.3
									Widowed, divorced, or separated	699	608		563		54		544	7			39	1,3		26	1,	18	1,005										7.3											5.2						7.1				12.8							11.1		84	828	768			70	724	849	1,3			30	1,3	97	1,420	1,286								9.2					9.6			9.7				8.7
									Never married	2,	457	2,	38	2,	209	2,067	2,1	32	2,705	4,011	4,			135	3,827	3,4		92											9.5						8.8	16.3	1											6.5		15.1	13.7	1,674	1,6				21	1,5	95	1,	4		96	1,	422	1,7			17	2,	424	2,		64	2,				61	2,533				9.1								8.3										7.7									7.2				12.5				11.8
White, 16 years and over	3,643	3,282	2,	93	2,	730	2,869	3,727	6,		421	6,	476	5,	631	4,931													4.4								4.2							7.4	2,668	2,5			65	2,			41	2,	271	2,			78	4,227	4,4	40	4,	257	3,9					85							4.8						7.5
1,		37	1,	161	1,011		89	965	1,		247	2,	4	98	2,	490	2,081	1,764					2.2														6.2	1,065	996	922		839		830	1,089	1,694	1,7	48	1,630	1,539												2.7		2.6
541	415	407	560	1,058	1,056		903	767								6.9		12.4		12.2	602	600		548	5		23	547	624	993	1,059	1,060		947			9.3
1,723	1,6		55	1,	505	1,428	1,483	1,	920	2,864	2,9		29	2,	648	2,399										7.8					9.9	14.8			13.4	1,001		969		949	909	897	1,070	1,	540	1,632	1,	567	1,	499								6.4
Black or African American, 16 years and over	860	844	774	752	1,		448	1,550	1,	502	1,	292		11.6				11.4				17.5	1						8.4	17.8	895	856	775	693	1,159	1,302	1,329	1,	252						10.2					9.8							8.9			13.8	1					4.1
	208			177				166			156		215		367			408	385	303							4.3		155			149		144			121	123	1		34	193	226		218
			118	104		119		105	205					239			217							10.6	16.4		17.9				13.3	1		80	179	178	246	265						10.9					10.7
	566		556	504		599		87	899	812	18.2	16.9			15.2	25.3	25.5	24.8	2	2.1	561	546	435	527	846		800	14.4	1			3.9			10.8		12.6		17.3		19.3
Asian, 16 years and over		204		153		141			110	306	305	269		249		162			124		125		216	238	250			234
	111		167	165	122	116		130
112		120					113				102				11.2					11.5
Hispanic or Latino ethnicity, 16 years and over		809	755	647	601	695		1,007	1,	670	1,711	1,527	1,383					7.6		12.7		587	480	525		672	1,036	1,	132		1,102	1,130			1		2.3
311			275		231		373	688		683	606	267		228		202		191	261	410	396
	223	213	175	12.3			127	103	126	243
	394	347	327	363	508	759	815	745	720	17.4	15.3		14.2	237	229	224		284	471	464	491		10.1		15.7
Total, 25 years and over	3,368	2,980	2,617	2,426	2,	538	3,	377	6,226	6,365	5,623	4,821	2,660	2,531	2,453	2,221	2,	198	2,717	4,279	4,603	4,490	4,234
1,676	1,399	1,232	1,088	1,152	1,	519	3,012	3,028	2,590	2,	207	1,233	1,139	1,071	955	959		1,227	1,933	2,050	1,910	1,815
675	584	520	515	708	1,262	1,305	1,141	961	792	781	667	1,263	1,334	1,353	1,229
1,017	997	848	819		871	1,150	1,952	2,032	1,892	1,654	634	611	651	691	1,083	1,219	1,	189		6.7
White, 25 years and over	2,	536	2,225	1,929	1,798	1,907	2,511	4,777	4,833	4,137	3,	555	1,853	1,773	1,699	1,578	1,579	1,964	3,	157	3,341	3,181	3,003
1,315	1,	108	966	854	919	1,191	2,413	2,407	2,013	1,710	911	845	765	756		1,006	1,		589	1,657	1,528	1,459
522	447	395		389		401	537	1,021		737			12.1	564	494	516	586	1,009	904
698	783	1,	358	1,404	1,			253	1,107	321	299	335	320	307		621	677	645	640
Black or African American, 25 years and over		568	545	507	473	1,022	1,082	883	14.7	15.5	951	878	11.9
		203	170	378	143	133	182	188
114		101		128		197	16.2	171	172	145	129	254	242		10.4
251		214			222	289	467	485	13.9	13.5	20.8		20.9	276	211	388	13.6	15.6	15.9
Asian, 25 years and over		117		131	176	187
109		163
Hispanic or Latino ethnicity, 25 years and over	477	387	455	1,218	1,226	1,068	941	439	372	330	355	816	788	805
345	655	582	174	154	374	390	386
100
210	362	324	312
NOTE: Estimates for the above race groups (white, black or African American, and Asian) do not sum to totals because data are not presented for all races. Persons whose ethnicity is identified as Hispanic or Latino may be of any race.