Practical 7.
Karl Pearson and Spearman’s Coefficients
Steps for the practical
Q 1.
Definition: Karl Pearson’s coefficient of correlation, also called as product moment
coefficient of correlation, is a statistical measure of how closely two variables are
related. It’s a wide used method in statistics to describe the strength and direction of
the relationship between two variables. It is calculated by dividing the covariance
between the two variables by the product of their standard deviations.
a)
1. In A1 write Σ(x - xbar)2, In B1 write120. In A2 write Σ(y - ybar)2, In B2 write 235. In
A3 write Σ(x - xbar)(y - ybar). In B3 write -139.
2. In A4 write r. In B4 calculate using the formula: =B3/(SQRT(B1)*SQRT(B2))
b)
1. In A6 write n. In B6 write 16. In A7 write Σx. In B7 write 12.3. In A8 write Σy. In B8
write 213. In A9 write Σxy. In B9 write 183. In A10 write Σx2. In B10 write 15.76. In
A11 write Σy2. In B11 write 3600.
2. In A12 write r. In B12 calculate using the formula: =(B9-B7*B8/B6)/(SQRT(B10-
B7*B7/B6)*SQRT(B11-B8*B8/B6))
c) This problem is to be done mathematically.
Given Σd2 = 22, R =0.9, n = ?
R = 1 – 6 Σd2/n(n2 – 1)
0.9 = 1 – 6 x 22 / n(n2 – 1)
132 / n(n2 – 1) = 0.1
n(n2 – 1) = 1320
By trial and error, n = 11.
Q 2.
1. In A1 write r. In B1 write 0.32. In A2 write Cov(x,y). In B2 write 15. In A3 write
Var(x). In B3 write 9.
2. In A4 write σx. In B4 calculate using the formula: =SQRT(B3)
3. In A5 write σy. In B5 calculate using the formula: =B2/(B1*B4)
Q 3.
1. In A1 write Sales experienc in years (x). In B1 write Commission earned in 1000s (y).
Fill the given data from A2:B7.
2. In A10 write r (using Excel formula). In B10 calculate using the formula:
=PEARSON(A2:A7,B2:B7)
3. We also calculate the correlation coefficient using the mathematical formula of Karl
Pearson.
4. In C1 write x2. In C2 calculate using the formula: = A2*A2. Drag and fill the column.
5. In D1 write y2. In D2 calculate using the formula: = B2*B2. Drag and fill the column.
6. In E1 write xy. In E2 calculate using the formula: = A2*B2. Drag and fill the column.
7. Find the totals of each of the 5 columns in A8, B8, C8, D8 and E8 respectively using
AutoSum.
8. In A11 write n. In B11 find the number of pairs of values given using the formula:
=COUNT(A2:A7). Do not consider the total value.
9. In A12 write r (using KPCC). In B12 calculate using the formula: =(E8-
A8*B8/B11)/(SQRT(C8-A8*A8/B11)*SQRT(D8-B8*B8/B11))
10. Note that both values are identical. In the exam you will be asked to solve either by
excel or by mathematical formula or both.
Q 4. Is identical to Q 3. Write the steps on your own.
Q 5.
1. In A1 write Rank 1. In B1 write Rank 2. Fill the given data from A2:B8.
2. In C1 write d = R1 – R2. In C2 calculate using the formula: =A2-B2. Drag and fill the
column.
3. In D1 write d2. In D2 calculate using the formula: =C2*C2. Drag and fill the column.
Find the total in D9 using AutoSum.
4. In A11 write n. In B11 find the number of pairs of values using the formula:
=COUNT(A2:A8)
5. In A12 write R. In B12 calculate using the formula: = 1 - (6*D9/(B11*(B11*B11-1)))
Q 6.
1. In A1 write Marks 1. In B1 write Marks 2. Fill the given data from A2:B8.
2. In C1 write Rank 1. In C2 calculate using the formula:
=[Link](A2,$A$2:$A$8). Drag and fill the column.
3. In D1 write Rank 2. In D2 calculate using the formula:
=[Link](B2,$B$2:$B$8). Drag and fill the column.
4. In E1 write d = R1 – R2. In E2 calculate using the formula: =C2-D2. Drag and fill the
column.
5. In F1 write d2. In F2 calculate using the formula: =E2*E2. Drag and fill the column.
Find the total in F9 using AutoSum.
6. In A11 write n. In B11 find the number of pairs of values using the formula:
=COUNT(A2:A8)
7. In A12 write R. In B12 calculate using the formula: = =1-(6*F9/(B11*(B11*B11-1)))
Q 7.
1. In A1 write X. In B1 write Y. Fill the given data from A2:B8.
2. In C1 write Rank 1. In C2 calculate using the formula:
=[Link](A2,$A$2:$A$8). Drag and fill the column.
3. In D1 write Rank 2. In D2 calculate using the formula:
=[Link](B2,$B$2:$B$8). Drag and fill the column.
4. In E1 write d = R1 – R2. In E2 calculate using the formula: =C2-D2. Drag and fill the
column.
5. In F1 write d2. In F2 calculate using the formula: =E2*E2. Drag and fill the column.
Find the total in F9 using AutoSum.
6. Now we make a small table to calculate the correction factor. In A11 write Rank
(which are repeated), in B11 write m (number of times the particular rank is repeated)
and in C11 write m(m2-1)/12.
7. In A12, A13 and A14 write 1.5, 6.5 and 1.5 respectively (the repeated ranks).
8. In B12, B13 and B14 write 2, 2 and 2 (number of times repeated). You may also use
COUNTIF function)
9. In C12 calculate using the formula: =B12*(B12*B12-1)/12. Drag and fill the column.
Calculate the total in C15 using AutoSum. (This total is Correction Factor CF).
10. In E11 write n. In F11 calculate using the formula: =COUNT(A2:A8)
11. In E12 write R. In F12 calculate using the formula: =1-(6*(F9+C15)/(F11*(F11*F11-
1)))
Note: Format tables where ever required by adding borders, centre alignment and making
header row bold.