Use the Excel directions below to create a scatterplot for the Birth year and Li

Question

Use the Excel directions below to create a scatterplot for the Birth year and Life Expectancy.

Please show Step-by-step how I would put this on Excel and the answers!

a) Find the Best Fit Line using Excel. State what the slope means with respect to the problem.

b) Find the Coefficient of Determination using Excel. Write what this means.

c) Find Se (=steyx(y’s, x’s)

d) Test the claim that there is a linear correlation between Birth year and Life Expectancy

using .01 level of significance. Show all hypothesis steps. Use Calculator (LinRegTtest) to find test value and Use Excel to find p-value (excel 4 homework).

Birth Year Life 1972 74.7 1975 76.6 1981 77.4 1987 78.2 1993 78.8 1995 79.9 2001 80.1 2005 80.3

Explanation / Answer

a.

Mean of X = X / n =    1659.5935
Mean of Y = Y / n =   65.5408
(Xi - Mean)^2 =   6611057.984
(Yi - Mean)^2 =   10328.09
(Xi-Mean)*(Yi-Mean) =   261064.6138
b1 = (Xi-Mean)*(Yi-Mean) / (Xi - Mean)^2    
Y = bo + b1 X  
  
Y'=0.005+0.0395*X  

b. Correlation

r( X,Y) =Co V ( X,Y) / S.D (X) * S.D (y)                              
r( X,Y) = Sum(XY) / N- Mean of (X) * Mean of (Y) / Sqrt( X^2/n - ( Mean of X)^2 ) Sqrt( Y^2/n - ( Mean of Y)^2 )                                
                              
Co v ( X, Y ) = 1 /8 (1245036.7) - [ 1/8 *15909 ] [ 1/8 *626] = 19.681                              
S. D ( X ) = Sqrt( 1/8*31638039-(1/8*15909)^2) = 11.202                              
S .D (Y) = Sqrt( 1/8*49011.2-(1/8*626)^2) = 1.827                              
r(x,y) = 19.681 / 11.202*1.827 = 0.9616                              
                              
If r = 0.9616> 0 ,Perfect Positive Correlation                              
                              
Coeffcient of determination = r^2 = 0.924675                              

d.

Given that,
value of r =0.9616
number (n)=8
null, Ho: =0
alternate, H1: !=0
level of significance, = 0.01
from standard normal table, two tailed t /2 =3.707
since our test is two-tailed
reject Ho, if to < -3.707 OR if to > 3.707
we use test statistic (t) = r / sqrt(1-r^2/(n-2))
to=0.9616/(sqrt( ( 1-0.9616^2 )/(8-2) )
to =8.58
|to | =8.58
critical value
the value of |t | at los 0.01% is 3.707
we got |to| =8.58 & | t | =3.707
make decision
hence value of | to | > | t | and here we reject Ho
ANSWERS
---------------
null, Ho: =0
alternate, H1: !=0
test statistic: 8.58
critical value: -3.707 , 3.707
decision: reject Ho

Line of Regression Y on X i.e Y = bo + b1 X X Y (Xi - Mean)^2 (Yi - Mean)^2 (Xi-Mean)*(Yi-Mean) 1972 74.7 97597.8212 83.8909 2861.3936 1975 76.6 99481.2602 122.3059 3488.1436 1981 77.4 103302.1382 140.6406 3811.624 1987 78.2 107195.0162 160.2553 4144.7044 1993 78.8 111159.8942 175.8064 4420.7035 1995 79.9 112497.5202 206.1866 4816.169 2001 80.1 116558.3982 211.9703 4970.6055 2005 80.3 119305.6502 217.834 5097.9236

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.