Question

The management of a regional bus line thought the company's cost of gas might be correlated with its passenger/mile ratio. Please follow the example video and fill the answers in the blue blanks. And use excel data analysis pane to check whether the answer is consistent.
								Year	Expenditures	Ratio
								1	56.5	8.37
								2	59.4	8.93
3	63	9.15
4	65.6	9.79
5	89	11.2

Step 1 - Determine the person (product-moment) correlation coefficient

r _____ Correlation

_____ Pearson

Step 2 Determine the number of pairs

n ____

Step 3 Determine the t value

t _____ t=r x sqrt(n-2/1-r^2)

Step 4 Determine the degree of freedom

df ____ df= n-2

Step 5 Determine the significance

p=sig ____ TDIST(ABS(t-value);df,2)

Answer 1

S.No	X	Y	(x-x̅)2	(y-y̅)2	(x-x̅)(y-y̅)
1	56.5	8.37	104.0400	1.25	11.4036
2	59.4	8.93	53.2900	0.31	4.0734
3	63	9.15	13.6900	0.11	1.2506
4	65.6	9.79	1.2100	0.09	-0.3322
5	89	11.2	497.2900	2.93	38.1776
Total	333.5	47.44	669.5200	4.70	54.5730
Mean	66.700	9.49	SSX	SSY	SXY

Step 1 -

correlation coefficient r=

Sxy/(√Sxx*Syy) =

0.9731

Step 2 n=5

step 3:

test stat t=

r*(√(n-2)/(1-r2))=

7.3149

step 4:

df =n-2=5-2 =3

Step 5 :

P value    =

0.0053