Chapter 4, Problem 93CE

(a) Make an Excel scatter plot of X = 1990 assault rate per 100,000 population and Y = 2004 assault rate per 100,000 population for the 50 U.S. states. (b) Use Excel’s =CORREL function to find the correlation coefficient. (c) What do the graph and correlation coefficient say about assault rates by state for these two years? (d) Use MegaStat or Excel to find the mean, median, and standard deviation of assault rates in these two years. What does this comparison tell you?

To determine

Construct a scatter plot for the assault rate per 100,000 populations using excel.

Answer to Problem 93CE

The scatter plot for the assault rate per 100,000 populations is as follows,

Explanation of Solution

Calculation:

The data set represents the assault rate per 100,000 population in the year 1990 and 2004 for the 50 U.S. states.

Scatter plot:

Software procedure:

Step-by-step software procedure to obtain the Scatter plot using EXCEL is as follows:

• Open an EXCEL file.
• In column A, enter the column of 1990, in column B enter the column of 2004.
• Select the data that is to be displayed.
• Click on Insert > select Scatter icon.
• Click on the chart > select Layout from the Chart Tools.
• Select Chart Title > Above Chart.
• Enter Assault Rate per 100,000 in 1990 vs 2004 in the dialog box
• Select Axis Title > Primary Horizontal Axis Title > Title Below Axis.
• Enter 1990 in the dialog box.
• Select Axis Title > Primary Vertical Axis Title > Rotated Title.
• Enter 2004 in the dialog box.
• Thus, the scatter plot for the assault rate per 100,000 populations has been obtained using Excel.

To determine

Find the correlation coefficient using Excel.

Answer to Problem 93CE

The correlation coefficient for the assault rate per 100,000 population in the years 1990 and 2004 is 0.8332.

Explanation of Solution

Calculation:

Correlation:

The correlation coefficient, r, between ordered pairs of variables, (x, y) having sample means $\overline{x}$ and $\overline{y}$, for a sample of size n is given as $r=\frac{{\displaystyle \sum _{i=1}^n\left(x_i-\overline{x}\right)\left(y_i-\overline{y}\right)}}{\sqrt{{\displaystyle \sum _{i=1}^n{\left(x_i-\overline{x}\right)}^2}}\sqrt{{\displaystyle \sum _{i=1}^n{\left(y_i-\overline{y}\right)}^2}}}$. The correlation coefficient is useful for measuring the strength of the linear relationship between the two variables.

Software procedure:

Step-by-step software procedure to obtain correlation coefficient using EXCEL software is as follows:

• Open an EXCEL file.
• Enter the data in the column I in cells I1 to I50 and column J in cells J1 to J50.
• In cell A1, enter the formula “=CORREL(I1:I50,J1:J50)”.
• Output using EXCEL software is given below:

Thus, the Correlation coefficient for the assault rate per 100,000 population between the years 1990 and 2004 is 0.8332.

To determine

Describe about the assault rates by the states for the year 1990 and 2004 as stated by the graph and correlation coefficient.

Explanation of Solution

Linear relationship:

If two variables are such that, when they are plotted on a scatterplot the data clusters around a straight line, then the variables are said to have a linear association or a linear relationship.

Interpretation of the correlation coefficient:

The correlation coefficient, r, between two linearly related variables is interpreted as follows:

• A positive value of r indicates a positive linear relationship.
• A negative value of r indicates a negative linear relationship.
• A value of r close to 0 indicates a weak linear relationship.
• A value of r close to 1 indicates a strong positive linear relationship.
• A value of r close to –1 indicates a strong negative linear relationship.
• The value $r=1$ indicates a perfect positive linear relationship.
• The value $r=-1$ indicates a perfect negative linear relationship.

From the previous part (b), the sample correlation coefficient is 0.8332.

A careful inspection of the scatterplot and correlation coefficient reveals that the data are clustered around a straight line and correlation coefficient is near to 1, indicating that there exists a strong positive linear relationship between the two variables considered, assault rate in the year 1990 and assault rate in the year 2004.

Thus, there is a strong positive linear relationship between the assault rate in the year 1990 and assault rate in the year 2004.

To determine

Calculate the sample mean, median and standard deviation.

Answer to Problem 93CE

The Mean, Median and Standard deviation are:

Measures	1990	2004
Mean	331.92	256.6
Median	307	232
Standard deviation	172.91	131.26

Explanation of Solution

Calculation:

For 1990:

Mean:

Software procedure:

Step-by-step software procedure to obtain Mean using EXCEL software is as follows:

• Open an EXCEL file.
• Enter the data in the column I in cells I1 to I50.
• In cell A1, enter the formula “=AVERAGE(I1:I50)”.
• Output using EXCEL software is given below:

Thus, the mean for the assault rate in 1990 is 331.92.

Median:

Software procedure:

Step-by-step software procedure to obtain Median using EXCEL software is as follows:

• Open an EXCEL file.
• Enter the data in the column I in cells I1 to I50.
• In cell A1, enter the formula “=MEDIAN(I1:150)”.
• Output using EXCEL software is given below:

Thus, the median for assault rate in 1990 is 307.

Standard deviation:

Software procedure:

Step-by-step software procedure to obtain Standard deviation using EXCEL software is as follows:

• Open an EXCEL file.
• Enter the data in the column I in cells I1 to I50.
• In cell A1, enter the formula “=STDEV(I1:I50)”.
• Output using EXCEL software is given below:

Thus, the standard deviation for assault rate in 1990 is 172.91.

For 2004:

Mean:

Software procedure:

Step-by-step software procedure to obtain Mean using EXCEL software is as follows:

• Open an EXCEL file.
• Enter the data in the column J in cells J1 to J50.
• In cell A1, enter the formula “=AVERAGE(J1:J50)”.
• Output using EXCEL software is given below:

Thus, the mean for the assault rate in 2004 is 256.60.

Median:

Software procedure:

Step-by-step software procedure to obtain Median using EXCEL software is as follows:

• Open an EXCEL file.
• Enter the data in the column J in cells J1 to J50.
• In cell A1, enter the formula “=MEDIAN(J1:J50)”.
• Output using EXCEL software is given below:

Thus, the median for assault rate in 2004 is 232.

Standard deviation:

Software procedure:

Step-by-step software procedure to obtain Standard deviation using EXCEL software is as follows:

• Open an EXCEL file.
• Enter the data in the column J in cells J1 to J50.
• In cell A1, enter the formula “=STDEV(J1:J50)”.
• Output using EXCEL software is given below:

Thus, the standard deviation for assault rate in 2004 is 131.26.

A comparison of the values of measures shows that the assault rate in 1990 was greater than that of the assault rate in 2004.