Question

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

Answer 1

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