(a) Make an Excel worksheet to calculate SS_xx, SS_yy and SS_xy. (Leave no cells
ID: 3231879 • Letter: #
Question
(a) Make an Excel worksheet to calculate SS_xx, SS_yy and SS_xy. (Leave no cells blank - be certain to enter "0" wherever required. Negative values should be indicated by a minus sign. Round your answers to 2 decimal places.) (b) Use the formulas to calculate the slope and intercept. (Round your answers to 3 decimal places.) (c) Use your estimated slope and intercept to make a worksheet to calculate SSE, SSR, and SST (Leave no cells blank be certain to enter "0" wherever required. Negative values should be indicated by a minus sign. Do not round intermediate calculations, but final answers should be rounded to 3 decimal places.) (d) Calculate the R^2. (Round your answer to 4 decimal places.)Explanation / Answer
obs
Xi
Yi
(xi-x)2
(yi-y)2
(xi-x)(yi-y)
1
13
100
40.96
7327.36
547.84
2
12
181
54.76
21.16
34.04
3
20
220
0.36
1183.36
20.64
4
18
165
1.96
424.36
28.84
5
34
262
213.16
5836.96
1115.44
Total
97
928
ssxx=311.2
SST=Ssyy=14793.2
Ssxy= 1746.8
Average
X = 19.4
Y=185.6
Calculate slope and intercept
b1 = Ssxy / ssxx = 1746.8/311.2 = 5.613
bo = y - b1*x = 185.6 – 5.613*19.4 = 76.708
Regression line is given by
yi = bo + b1*Xi; i = 1,2,3,4,5 where y is the predicted value
xi
yi
y
yi-y
(yi-y)2
13
100
149.6760925
-49.67609254
2467.714171
12
181
144.062982
36.93701799
1364.343298
20
220
188.9678663
31.03213368
962.9933205
18
165
177.7416452
-12.74164524
162.3495235
34
262
267.5514139
-5.551413882
30.81819609
SSE= 4988.218509
X
19.4
y
185.6
SSE
4988.219
SSR
b1*SSxy = 5.613*1764.8=9804.7884
SST
SSE+SSR = 14793.2
R2 = SSR / SST = 0.6628
obs
Xi
Yi
(xi-x)2
(yi-y)2
(xi-x)(yi-y)
1
13
100
40.96
7327.36
547.84
2
12
181
54.76
21.16
34.04
3
20
220
0.36
1183.36
20.64
4
18
165
1.96
424.36
28.84
5
34
262
213.16
5836.96
1115.44
Total
97
928
ssxx=311.2
SST=Ssyy=14793.2
Ssxy= 1746.8
Average
X = 19.4
Y=185.6
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.