Question

A researcher would like to predict the dependent variable Y from the two independent variables X and X2 for a sample of N 10 subjects. Use multiple linear regression to calculate the coefficient of multiple determination and test statistics to assess the significance ofthe regression model and partial slopes. Use a significance level a 0.02. Y 39.6 38.2 72.2 43.8 63.6 10.2 39.6 59.1 28.8 78.9 35.5 63.2 43.1 73.4 66.1 48.2 42.9 72.7 53 63.1 53.7 44 33.5 36.2 42.5 46.5 30 45 33.6 43.6 F P-value for overall model t,= for b. P-value = to for b, P-value What is your conclusion for the overall regression model (also called the omnibus test)? The overall regression model is statistically significant at 0,02. The overall regression model is not statistically significant at a 0.02. Which of the regression effici are statistically different from zero Oneither regression coefficient is statistically significant the slope for the first variable b is the only statistically significant coefficient the slope for the second variable b is the only statistically significant coefficient Oboth regression coefficients are statistically significant

Please explain how to do this in excel

Answer 1

put the above data in excel: goto :data : data analysis: regression: put Y column in input y : and x1 and x2 in input x columns ; check lebel and press ok

you will get below output:

Regression Statistics
Multiple R	0.806770156
R Square	0.650878085
Adjusted R Square	0.551128966
Standard Error	3.859014743
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	2	194.3450365	97.17251826	6.525151242	0.025143162
Residual	7	104.2439635	14.89199478
Total	9	298.589
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	55.92565953	8.424624629	6.638356246	0.00029367	36.00458782	75.84673124	36.00458782	75.84673124
x1	0.040456942	0.066596046	0.607497665	0.562701309	-0.117017682	0.197931567	-0.117017682	0.197931567
x2	-0.334974336	0.114180275	-2.93373208	0.021907657	-0.604967784	-0.064980888	-0.604967784	-0.064980888

from above:

R² =0.6509

F =6.5252

p value for overall model =0.0251

t1 =0.6075

for t1 , p value =0.5627

t2 =-2.9337

for t1 , p value =0.0219

the overall regression model is not statistically significant

neither regression coefficient is statistically significant