In: Statistics and Probability
y | x1 | x2 | x3 |
College Ranking | Annual Cost | Admission Rate | 4Yrs Grad. |
98 | $37,330 | 84 | 66 |
143 | $44,570 | 82 | 70 |
153 | $54,464 | 85 | 72 |
193 | $35,074 | 29 | 41 |
198 | $33,160 | 89 | 45 |
201 | $46,852 | 76 | 63 |
208 | $24,053 | 82 | 42 |
224 | $48,068 | 76 | 64 |
229 | $40,788 | 78 | 62 |
256 | $54,148 | 85 | 65 |
258 | $34,745 | 82 | 54 |
261 | $25,309 | 86 | 34 |
269 | $29,328 | 81 | 40 |
279 | $35,934 | 85 | 38 |
286 | $49,060 | 83 | 61 |
293 | $40,730 | 97 | 49 |
299 | $31,908 | 22 | 20 |
302 | $32,310 | 77 | 26 |
303 | $37,322 | 78 | 47 |
304 | $38,654 | 81 | 44 |
305 | $27,152 | 86 | 36 |
310 | $43,355 | 76 | 54 |
314 | $49,608 | 84 | 52 |
319 | $47,630 | 79 | 61 |
334 | $47,090 | 92 | 59 |
339 | $40,958 | 78 | 54 |
340 | $43,868 | 83 | 50 |
355 | $42,896 | 77 | 45 |
356 | $27,445 | 83 | 36 |
357 | $49,587 | 76 |
52 |
1. Run Multiple Regression for X1X2X3 Write regression equation and coefficient of determination.Explain equation and r squared.
2. Write equation and coefficient of determination, then select 3 sets of numbers and predict the values of Y. (Remember the range.)
3. Calculate +-1, +-2, and +-3 standard deviations from the mean (Use Descriptive Statistics.), Identify outliers and discuss F significance
We will be applying the Linear regression model here, it can be done by using the function LINEST(y_value, x_value, TRUE, TRUE) where y_values contain values of y here and x_values have x1, x2, x3 values.
Select 5 rows and 4 columns and then write the formula in the first cell and after that, press Shift + Ctrl + Enter.
The equation comes out to be-
y = 180.1385 + 0.007*x1 + 1.72*x2 - 6.55*x3
Coefficient of determination(R**2) comes out to be 0.5465, which means change in y can be 54.65% explained by x1, x2 and x3.
Taken 3 values of x1, x2, and x3 and predicted y here -
x1 | x2 | x3 | y(predicted) | y |
24053 | 82 | 42 | 216.804649 | 208 |
48068 | 76 | 64 | 233.01582 | 224 |
40788 | 78 | 62 | 197.818698 | 229 |
y predicted was calculated like this -
y(predicted) = 180.1385+(0.007107*L6)+(1.718549*M6)-(6.55237*N6)
mean | 269.5333 |
S.D | 67.31743 |
mean + S.D | 336.8508 |
mean - S.D | 202.2159 |
mean + 2*S.D | 404.1682 |
mean - 2*S.D | 134.8985 |
mean + 3*S.D | 471.4856 |
mean - 3*S.D | 67.58103 |