Question

you must calculate the deviations from the average, the manual correlation calculation and cross check it with Excel's correl function. Furthermore, you will generate the scatterplot graph, along with the trendline. Indicate whether there is a strong/weak/no correlation between the two variables.

Question:

The following tables shows the mpg and curbed weight, in thousands for eight randomly selected vehicles:

MPG:                    28           22           20           34           41           15           19           15

Weight:                2730       2860       3140       2180       2310       4450       3920       2590

Using XLS, calculate the correlation coefficient for this sample. Using an significance of .1, test the significance of the population correlation coefficient is negatively related between mpg and curbed weight. What conclusions can you draw?

Answer 1

The correlation coefficient for this sample is obtained using excel as follows :
i.e r = CORREL(A26:A33,B26:B33) = -0.6825

(Manually)
In general,
r =  
From the data,
n = 8,   = 551070,   = 194,   = 24180,   = 5316,   = 77457600

r = -0.6825

The graph also suggests a strong negative correlation between the two variables.

Ho : r = 0
Ha : r < 0

Level of Significance(l.o.s.) : = 0.01

Decision Criteria : Reject Ho at 1% l.o.s. if t cal < t tab,
where t tab = t (, n-2) = t (0.01, 6) = -3.1426

Test statistic : t cal =   = -2.2873

Conclusion : Since t cal > t tab, we fail to reject Ho at 1% l.o.s. & thus, the population correlation coefficient is not negatively related between mpg and curbed weight.