On the first sheet, which is labeled DATA, of the file StateCrimeDataSet.xlsx ar
On the first sheet, which is labeled DATA, of the file StateCrimeDataSet.xlsx are urban crime data for each of the 50 states in columns named URBAN (percent living in urban areas), MURDER (murders per million residents), ROBBERY (robberies per million residents), POLICE (police employees per 100,000 population), and REGION.
You will work with these data and find variance, standard deviation, skewness, kurtosis, and create a frequency distribution for univariate (single variable) statistics. You will then find the correlation coefficient and create a scattergram for bivariate (two variable) data.
Variance measures how far a set of numbers is spread out. A variance of zero indicates that all the values are identical. Variance is always non-negative: a small variance indicates that the data points tend to be very close to the mean and hence to each other, while a high variance indicates that the data points are very spread out around the mean and from each other.
Standard deviation is a measure that is used to quantify the amount of variation or dispersion of a set of data values] A low standard deviation indicates that the data points tend to be very close to the mean (also called the expected value) of the set, while a high standard deviation indicates that the data points are spread out over a wider range of values. Standard deviation is the square root of the variance.
Skewness is a measure of the asymmetry of the probability distribution of a real-valued random variable about its mean. The skewness value can be positive or negative, or even undefined.
Kurtosis is any measure of the “peakedness” of the probability distribution of a real-valued random variable. In a similar way to the concept of skewness, kurtosis is a descriptor of the shape of a probability distribution.
The formulas for skewness and kurtosis can be written as:
A Correlation Coefficient is a measure of the linear dependence between two variables X and Y, giving a value between +1 and −1 inclusive, where 1 is total positive correlation, 0 is no correlation, and −1 is total negative correlation.
We will cover these concepts more thoroughly in later chapters
Flag question: Spacer
Variance and Standard Deviation
On the DATA sheet, select the cells A1:B52.
Right click and select Copy.
Select the sheet named Sheet2.
With the mouse, select select cell A1.
Right click and under Paste Options select Paste (P) (the leftmost icon) to copy the data into cells A1:B52.
Rename the sheet to UNIVARIATE.
Calculate the variance for MURDER by breaking down the formula where (Xbar) is the mean, (Xi) is each of the samples, and (N) is the number of samples. Enter exactly as shown the following labels in the labeled cells.
A54
N =
A55
Mean =
A56
Variance=
A57
Std Dev=
C54
Sums:
C2
(Xi-Xbar)
D2
(Xi-Xbar)^2
Into the indicated cells, enter exactly as shown, the following formulas. The comments in red, in the third column of the table, do not get entered into your spreadsheet.
B54
=count(b2: b52)
This counts the number of cells with data.
B55
=sum(b2: b52)/b54
This finds the mean value by adding all the values and dividing by the number of values.
Enter the following formula into C3. The comments in red, in the third column of the table, do not get entered into your spreadsheet.
C3
=b3-$b$55
This finds the difference between the value and the mean.
Copy the formula in cell C3 into cells C4 to C52. Click on cell C3. A box appears around the cell. In the lower right corner of that box is a very small square. Hover the mouse over that square. The mouse pointer will change to a plus sign. Click on the little square and drag in down all the way to cell C52. The cells now will be updated with the new, modified formulas.
Enter the following formula into cell D3. The comments in red, in the third column of the table, do not get entered into your spreadsheet.
D3
=c3^2
This squares the difference.
Copy the formula in cell D3 into cells D4 to D52. Follow the same steps as step 10 only using column D cells.
Enter the following formula in cell D54. The comments in red, in the third column of the table, do not get entered into your spreadsheet.
D54
=sum(d3:d52)
This sums the squares of the differences.
This is the sum of the squared deviations, or the numerator for the variance equation in step 3 above. (Note: Steps 8 to 13 could also be accomplished with the function:
D55
=sumproduct(c3:c52,c3:c52)
Enter the above formula into cell D55. Do the two cells contain the same values?
Enter the following formula into cell B56 to calculate the variance for the variable MURDER.
B56
=d54/(b54-1)
The standard deviation is simply the square root of the variance which can be calculated with the following formula in cell B57.
B57
=sqrt(b56)
Skewness and Kurtosis
Enter the following labels into the cells indicated below.
A58
Skewness=
A59
Kurtosis=
E2
(Xi-Xbar)^3
F2
(Xi-Xbar)^4
Enter the following formula into cell E3.
E3
=c3^3
Copy the formula in cell E3 into cells E4 to E52 using the procedure of step 10.
Enter the following formula into cell F3.
F3
=c3^4
Copy the formula in cell F3 into cells F4 to F52 using the procedure of step 10.
Enter the following formula into the indicated cells.
E54
=sum(e3:e52)
F54
=sum(f3:f52)
Enter the following formulas into the indicated cells.
B58
=(b54/((b54-1)*(b54-2)))*(e54/(b57^3))
B59
=(((b54*(b54+1))/((b54-1)*(b54-2)*(b54-3)))*(f54/(b57^4)))-((3*(b54-1)*(b54-1))/((b54-2)*(b54-3)))
Flag question: Question 1Question 15 pts
Descriptive Statistics Shortcut:
Excel has built in functions to accomplish much of what was done in the previous section.
Enter the following labels into the indicated cells..
A63
Shortcuts:
A64
Min. =
A65
Max. =
A66
Mode =
A67
Median =
A68
Mean =
A69
Variance=
A70
Std Dev.=
A71
Skewness=
A72
Kurtosis=
Enter the following formulas into the indicated cells.
B64
=min(b3:b52)
B65
=max(b3:b52)
B66
=mode(b3:b52)
B67
=median(b3:b52)
B68
=average(b3:b52)
B69
=var.s(b3:b52)
B70
=stdev.s(b3:b52)
B71
=skew(b3:b52)
B72
=kurt(b3:b52)
Do these values agree with the values found earlier?
YesNo
Flag question: Question 2Question 25 pts
Frequency Distribution
Create a frequency distribution for the variable MURDER. Enter the following into the cells indicated below.
H3
50
H4
100
H5
150
H6
200
I3
0-50
I4
51-100
I5
101-150
I6
151-200
I7
Total
J2
Frequency
To create a frequency distribution you must enter an array formula (a formula that is entered into an array of cells). The intervals for the distribution are in cells I3 to I6. The upper value in the range for each interval is listed in cells H3 to H6. the format of the frequency function is: =FREQUENCY(data_range,interval_range)
To enter an array formula, highlight the cells into which the frequency distribution will be located. In this case highlight cells J3 to J6. The cells J3 to J6 will appear as grey.
Type the following formula (but do not press ENTER when you finish). The formula will appear in the status bar at the top of the spreadsheet.
=frequency(b3:b52,h3:h6)
After you finish typing in the formula press the following three keys simultaneously: CONTROL, SHIFT, and ENTER. The frequency for each interval should appear in the cells J3 to J6.
To complete the frequency distribution enter the following formula.
J7
=sum(j3:j6)
The entry in cell J7 is the total number of value. Is the value in cell J7 equal to 50?
YesNo
Flag question: Spacer
Bivariate Statistics
Return to the DATA sheet and copy cells A1:A52 into cells A1:A52 of Sheet3
Copy cells D1:E52 from DATA and paste this data for the variables B1:C52 on Sheet3.
Label Sheet3 as BIVARIATE.
Calculate a Pearson’s product moment correlation coefficient () for the variables ROBBERY and POLICE by breaking down the formula for Pearson’s which is
Enter the following labels into the indicated cells
A54
N =
A55
Mean =
A56
Std Dev =
A57
Covar XY=
A58
Var X=
A59
Var Y=
A60
Pearson r=
D2
(Xi-Xbar)
E2
(Yi-Ybar)
F2
(Yi-Ybar)(Xi-Xbar)
G2
(Xi-Xbar)^2
H2
(Yi-Ybar)^2
Enter the following formulas into the indicated cells.
B54
=count(b3:b52)
B55
=average(b3:b52)
B56
=stdev.s(b3:b52)
C54
=count(c3:c52)
C55
=average(c3:c52)
C56
=stdev.s(c3:c52)
In cell D3 enter the formula
D3
=b3-$b$55
Copy the formula in cell D3 to cells D4 to D52 using the procedure you learned earlier.
Enter the following formula into E3
E3
=c3-$c$55
Copy the formula in cell E3 to cells E4 to E52.
Enter the following formula into F3.
F3
=d3*e3
Copy the formula in cell F3 to cells F4 to F52.
Enter the following formula into G3.
G3
=d3^2
Copy the formula in cell G3 to cells G4 to G52.
Enter the following formula into cell H3.
H3
=e3^2
Copy the formula in cell H3 to cells H4 to H52.
Enter the following formulas into the indicated cells.
B57
=sum(f3:f52)/(b54-1)
B58
=sum(g3:g52)/(b54-1)
B59
=sum(h3:h54)/(b54-1)
B60
=b57/sqrt(b58*b59)
Flag question: Question 3Question 35 pts
Shortcut to Pearson r
A much simpler method of calculating this correlation coefficient is to use the PEARSON function in Excel. Enter the following formula into cell B61
B61
=pearson(b3:b52,c3:c52)
Does the value is B61 agree with the value in B60?
YesNo
Flag question: Spacer
Scatterplot
Create a scatter plot for ROBBERY (as Y) and POLICE (as X).
Return to the DATA sheet.
Select the data to plot D2:E52
Open the INSERT menu and select Scatter from the Charts menu. The far left chart under Scatter.
Format the chart using the Chart Elements tool and set: Chart title: Scattergram of Robbery and Police; x-axis title: Police; y-sxis title: Robbery
Turn off the Legend if it appears.