Question

Calculate the sample correlation and covariance for (i) Final_exam and assignment_grade; (ii) Final_exam and Tutorial_attend; and (iii) assignment_grade and Tutorial_ attend, Carefully interpret your results. Once again use Excel for these calculations using both methods i.e. Data Analysis Tool Pack (options covariance, correlation) and using Excel formulae without use of Data Analysis Tool Pack. (Hint- if you find a discrepancy in your answers between the 2 excel methods can you explain why?)

Final_exam	assignment_grade	Tutorial_attend
100	90	5
100	75	5
90	75	5
85	85	5
85	100	5
80	95	5
70	80	5
60	95	5
60	80	5
55	95	5
55	25	4
50	80	5
45	90	5
40	65	5
40	65	4
35	0	3
30	70	4
30	55	4
25	85	5
25	90	4
15	5	3
15	80	5
15	50	5
15	45	3
5	75	3
5	70	4
100	100	5
95	75	5
90	100	5
85	85	5
80	95	5
70	45	5
70	100	5
65	90	5
60	100	5
55	65	4
55	90	5
55	80	4
50	50	5
45	50	4
45	75	3
40	75	5
40	70	5
35	90	4
30	95	5
30	55	5
25	75	4
25	20	3
25	65	2
15	60	4
15	60	4
15	80	5
10	55	4
10	80	2
0	0	2

Answer 1

i). ii) and iii).

I had run the analysis tool pack for all the three correlation simultaneously using the the range option in EXCEL and teh following output was generated.

Correlation by Tool Pack
	Final	Assign	Tutorial
Final	1
Assign	0.471843	1
Tutorial	0.592801	0.582391	1

The correlation between Final test scores and Assignment scores is 0.4718 and is positive indicating that both are positively related. The correaltion between Final test and the tutorail attended is 0.5928 and also the correaltion between Assignment and final test scores is 0.5824. Which is an indication that the more the tutorials attended, teh better are the assignment and final examination scores. Of course, though there is a positive correlation between Assignment and final examination, it is lesser when compared with the other two.

When we use the EXECL formulae, the correaltion results did not show any difference.  

Correlation by Tool Pack				By EXCEL formula
	Final	Assign	Tutorial				Final	Assign	Tutorial			Final	Assign	Tutorial
Final	1					Final	1				Final	0
Assign	0.471843	1				Assign	0.471843	1			Assign	0	0
Tutorial	0.592801	0.582391	1			Tutorial	0.592801	0.582391	1		Tutorial	0	0	0

The covariance however had shown a difference as below:

Covariance by EXCEL function				Covariance by EXCEL formula				Difference
	Final	Assign	Tutorial				Final	Assign	Tutorial			Final	Assign	Tutorial
Final	789.1405					Final	803.7542				Final	-14.6137
Assign	327.1488	609.1736				Assign	333.2071	620.4545			Assign	-6.05831	-11.281
Tutorial	14.67769	12.66942	0.77686			Tutorial	14.94949	12.90404	0.791246		Tutorial	-0.27181	-0.23462	-0.01439

We can see that there are differences in Covariance values eventhough the correlation coefficients are same.

After a close look at the values reported, the Analysis Tool pack had used the Population Variance and covariance ie using N as the divisor and the covar function in EXCEL used sample(now both options are also available).

The correlation values are same since when using the formula for it cancels out in the numerator and denominator..