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 selling prices, in thousands of dollars, and the square footages of seven randomly selected homes recently sold by Century 21 Realtors:

Price:     258         191         253         168         249         245         282

Sqr Ft:   2730       1860       2140       2180       2310       2450       2920

Using XLS, calculate the correlation coefficient for this sample. Using an significance of .1, test the significance of the population correlation coefficient between a house’s selling price and its square footage. What conclusions can you draw?

Answer 1

( X)	( Y)	X^2	Y^2	X*Y
258	2730	66564	7452900	704340
191	1860	36481	3459600	355260
253	2140	64009	4579600	541420
168	2180	28224	4752400	366240
249	2310	62001	5336100	575190
245	2450	60025	6002500	600250
282	2920	79524	8526400	823440

calculation procedure for correlation
sum of (x) = ∑x = 1646
sum of (y) = ∑y = 16590
sum of (x^2)= ∑x^2 = 396828
sum of (y^2)= ∑y^2 = 40109500
sum of (x*y)= ∑x*y = 3966140
to caluclate value of r( x,y) = covariance ( x,y ) / sd (x) * sd (y)
covariance ( x,y ) = [ ∑x*y - N *(∑x/N) * (∑y/N) ]/n-1
= 3966140 - [ 7 * (1646/7) * (16590/7) ]/7- 1
= 9302.857
and now to calculate r( x,y) = 9302.857/ (SQRT(1/7*3966140-(1/7*1646)^2) ) * ( SQRT(1/7*3966140-(1/7*16590)^2)
=9302.857 / (37.384*336.197)
=0.74
value of correlation is =0.74
coefficient of determination = r^2 = 0.548
properties of correlation
1. If r = 1 Corrlation is called Perfect Positive Correlation
2. If r = -1 Correlation is called Perfect Negative Correlation
3. If r = 0 Correlation is called Zero Correlation
& with above we conclude that correlation ( r ) is = 0.7402> 0 ,perfect positive correlation
              
Given that,
value of r =0.74
number (n)=7
null, Ho: ρ =0
alternate, H1: ρ!=0
level of significance, α = 0.1
from standard normal table, two tailed t α/2 =2.015
since our test is two-tailed
reject Ho, if to < -2.015 OR if to > 2.015
we use test statistic (t) = r / sqrt(1-r^2/(n-2))
to=0.74/(sqrt( ( 1-0.74^2 )/(7-2) )
to =2.46
|to | =2.46
critical value
the value of |t α| at los 0.1% is 2.015
we got |to| =2.46 & | t α | =2.015
make decision
hence value of | to | > | t α| and here we reject Ho
ANSWERS
---------------
null, Ho: ρ =0
alternate, H1: ρ!=0
test statistic: 2.46
critical value: -2.015 , 2.015
decision: reject Ho
we have enough evidence to support the claim that the significance of the population correlation coefficient between a house’s selling price and its square footage.