Question

Determine the Best Fit Linear Regression Equation Using Technology - Excel

Question

The table shows the age in years and the number of hours slept per day by 24 infants who were less than 1 year old. Use Excel to find the best fit linear regression equation, where age is the explanatory variable. Round the slope and intercept to one decimal place.

Age

Hours

0.03

16.5

0.05

15.2

0.06

16.2

0.08

15.0

0.11

16.0

0.19

16.0

0.21

15.0

0.26

14.5

0.34

14.6

HelpCopy to ClipboardDownload CSV

Provide your answer below:

Answer 1

To do regression analysis in Excel first goto data menu and then DataAnalysis under which you have to select regression.

Now select dependent variable in Y and independent in X.

The dependent variable is hours of sleep and independent variable is age.

Regression Statistics
Multiple R	0.677634
R Square	0.459188
Adjusted R Square	0.381929
Standard Error	0.580535
Observations	9

ANOVA
	df	SS	MS	F	Significance F
Regression	1	2.00308	2.00308	5.943499	0.044895
Residual	7	2.359142	0.33702
Total	8	4.362222

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	16.13266	0.342254	47.13647	5.06E-10	15.32336	16.94197
Age	-4.65712	1.910278	-2.43793	0.044895	-9.17421	-0.14003

the p-value in ANOVA table is less than 0.05 hence the model is significant.

The regression equation is Hours of sleep = 16.1 - 4.7*Age.