The Data

STATE	POLICE (X)	ROBBERY (Y)	MURDER (Y)
AL	230	1321	132
AK	350	900	97
AZ	310	1936	103
AR	190	809	92
CA	270	3842	143
CO	270	1601	69
CT	260	2180	47
DE	280	1370	69
FL	300	3555	145
GA	240	1976	138
HI	290	1902	87
ID	240	468	31
IL	320	2170	106
IN	210	1414	89
IA	200	549	22
KS	230	1131	69
KY	200	952	88
LA	290	1970	157
MA	290	2355	28
MD	310	3927	95
ME	200	308	41
MI	250	2440	102
MN	190	991	26
MS	200	810	145
MO	280	2236	111
MT	240	340	40
NE	220	822	44
NH	240	420	20
NV	360	4606	25
NJ	350	3037	69
NM	280	1279	131
NY	370	6413	127
NC	220	823	106
ND	180	77	12
OH	210	2237	81
OK	230	1049	51
OR	240	1524	51
PA	240	1779	68
RI	280	1186	44
SC	240	1181	114
SD	200	201	7
TN	210	1806	108
TX	240	2085	169
UT	240	802	38
VT	200	389	22
VA	230	1201	86
WA	210	1351	55
WV	180	485	71
WI	240	707	29
WY	310	444	62

Computer Assignment 2

Needed Statistics functions in EXCEL are listed below:

To Find	Enter*
Variance	=VAR.P(A1:A10)
Standard Deviation	=STDEV(A1:A10)
Mean	=AVERAGE(A1:A10)
Mode	=MODE(A1:A10)
Minimum Value	=QUARTILE(A1:A10,0)
Quartile 1	=QUARTILE(A1:A10,1)
Quartile 2	=QUARTILE(A1:A10,2)
Quartile 3	=QUARTILE(A1:A10,3)
Maximum Value	=QUARTILE(A1:A10,4)
Coefficient of Skew	=SKEW(A1:A10)
Range	=MAX(A1:A10) - MIN(A1:A10)
Interquartile Range	=QUARTILE(A1:A10,3) - QUARTILE(A1:A10,1)
	* In each case, the (A1:A10) indicates the data of interest.

Exercise 1:

• Open your Computer Assignment 1 Excel Data File. Save the file to your thumb drive or to a location on the computer that you will remember later (i.e. pictures)
• Format all numbers and text as Font: Calibri Size 11. Easily done by clicking on the downward triangle in the upper left corner (selects the entire worksheet), then choosing the font and size.

• Enter the titlesMinimum Value = Quartile 1 = Quartile 2= Quartile 3= Maximum Value= in CellsH6 throughH10 andSkew= Variance = Standard Deviation = Range =IQR = in CellsJ6 throughJ10.
• Bold the text for ColumnH andJ and Adjust the column width so all information is visible.
• Enter the corresponding formulas for the above titles for columnH in CellsI6 throughI10. Then, enter the corresponding formulas for the above titles for columnJ in Cells K6 throughK10. BE SURE TO CHANGE THE DATA OF INTEREST TO READ(B2:B51) FOR EACH FORMULA

Example: CellI6 should contain =QUARTILE(B2:B51,0)

• Format CellsI6 throughI10 andK6 thoughK10 as Number with one decimal place beyond the original data (in column B). Adjust the column width so all information is visible.

• Enter the titlesMinimum Value = Quartile 1 = Quartile 2= Quartile 3= Maximum Value= in CellsH14 throughH18 andSkew = Variance = Standard Deviation = Range =IQR = in CellsJ14 throughJ18.
• Bold the text for ColumnH andJ and Adjust the column width so all information is visible.
• Enter the corresponding formulas for the above titles for columnH in CellsI14 throughI18. Then, enter the corresponding formulas for the above titles for columnJ in Cells K14 throughK18. BE SURE TO CHANGE THE DATA OF INTEREST TO READ(C2:C51) FOR EACH FORMULA

Example: CellI14 should contain =QUARTILE(C2:C51,0)

• Format CellsI14 throughI18 andK14 thoughK18 as Number with one decimal place beyond the original data (in column C). Adjust the column width so all information is visible.

• Enter the titlesMinimum Value = Quartile 1 = Quartile 2= Quartile 3= Maximum Value =in CellsH22 throughH26 andSkew = Variance = Standard Deviation = Range =IQR = in CellsJ22 throughJ26.
• Bold the text for ColumnH andJ and Adjust the column width so all information is visible.
• Enter the corresponding formulas for the above titles for columnH in CellsI22 throughI26. Then, enter the corresponding formulas for the above titles for columnJ in Cells K22 throughK26. BE SURE TO CHANGE THE DATA OF INTEREST TO READ(D2:D51) FOR EACH FORMULA

Example: CellI22 should contain =QUARTILE(D2:D51,0)

• Format CellsI22 throughI26 andK22 thoughK26 as Number with one decimal place beyond the original data (in column D). Adjust the column width so all information is visible.

• Save the file and email it to yourself for your records. You will use this file for all other computer assignments this semester.

Exercise 2:

• Answer the items on Computer Assignment 2: Exercise 2 Assessment

Q1. The mean for Police is ; for Robbery is ; for Murder is .

Q2. The standard deviation for Police is ; for Robbery is ; for Murder is .

Q3. The coefficient of skew for Police is ; for Robbery is ; for Murder is .

Q4. The range for Police is ; for Robbery is ; for Murder is .

Q5. The interquartile range for Police is ; for Robbery is ; for Murder is .

Q6. Which variable, Police, Murder, Robbery, has more variablility (dispersion, wide-spread)?

	A.	Police
	B.	Robbery
	C.	Murder

Q7. Based on the coefficient of skew results in Excel, interpret the skew of Police, Robbery, and Murder (normal, positive, negative)

The skew for Police is ; for Robbery is ; for Murder is .