Question

A statistics instructor wants to examine the relationship between the hours a student spends studying for the final exam (Hours) and a student's grade on the final exam (Grade). She takes a sample of five students.

	Student	x (Hours)	y (Grade)
	1	8	75
	2	2	47
	3	3	50
	4	15	88
	5	25	93

a. Computer the Average and Standard Deviation for x and y USING EXCEL.

b. Computer the sample covariance for x & y USING EXCEL

c. Compute the sample correlation coefficient USING EXCEL

d. Specify the competing hypotheses to determine whether the hours spent studying and the final grade are correlated. USING EXCEL

e. Calculate the value of the test statistic and approximate the corresponding p-value. USING EXCEL

f. At the 10% significance level, what is the conclusion to the test? Explain. USING EXCEL

Please show me how to solve this problem using Excel.

Answer 1

A)

EXCEL formula =AVERAGE(P8:P12)

mean = 10.6

EXCEL formula =STDEV.S(P8:P12)

std dev = 9.5555

............

b)

EXCEL formula=COVARIANCE.S(P8:P12,Q8:Q12)

covariance = 186.8

.................

c)

formula = =CORREL(P8:P12,Q8:Q12)

r = 0.9202

...............

d)

correlation hypothesis test          
Ho:   ρ = 0   
Ha:   ρ ╪ 0      
  

e)

t-test statistic = r*√(n-2)/√(1-r²) =        4.073  

df= n-2 = 3
p-value =    0.0267    [Excel formula =t.dist(t-stat,df) ]

f)

Decison:   p value < α , So, Reject Ho      

................

Please revert back in case of any doubt.

Please upvote. Thanks in advance.