Question

LAB9B.DAT

1	10	0.802
2	12	2.176
3	7	0.261
4	13	1.618
5	13	2.033
6	15	4.094
7	15	2.201
8	8	0.902
9	8	1.185
10	12	1.734
11	3	0
12	10	1.477
13	19	3.801
14	13	1.732
15	14	2.747
16	10	0.304
17	7	1.627
18	11	1.726
19	0	0
20	12	2.222
21	14	2.908
22	12	2.261
23	11	0.972
24	13	1.779
25	10	1.194
26	15	3.447
27	14	2.496
28	8	1.015
29	17	4.558
30	6	0
31	8	2.081
32	10	1.758

■ Regression analysis, where one variable depends on another, can be used to predict levels of a dependent variable for specified levels of an independent variable. Use the EXCEL REGRESSION command to calculate the intercept and slope of the leastsquares line, as well as the analysis of variance associated with that line. Fill in the following table and use the results to answer the next few questions. Carefully choose your independent and dependent variables and input them correctly using EXCEL’s regression command. In this example, the percentage of drivers under the age of 21 affects the number of Fatals/1000 licenses.

The regression equation (leastsquares line) is

Fatals/1000 licenses = + % under 21

(intercept) (slope)

10. What is the estimated increase in number of fatal accidents per 1000 licenses due to a one percent increase in the percentage of drivers under 21 (i.e. the slope)?

11. What is the standard deviation of the estimated slope?

12. What is the estimated number of fatal accidents per 1000 licenses if there were no drivers under the age of 21 (i.e. the y intercept)?

13. What percentage of the variation in accident fatalities can be explained by the linear relationship with drivers under 21 (i.e. 100 ◊ the unadjusted coefficient of determination)?

Note about data:

In a study of the role of young drivers in automobile accidents, data on percentage of licensed drivers under the age of 21 and the number of fatal accidents per 1000 licenses were determined for 32 cities. The data are stored in Table B. The first column contains a number as the city code, the second column contains the percentage of drivers who are under 21, and the third column contains the number of fatal accidents per 1000 drivers. The primary interest is whether or not the number of fatal accidents is dependent upon the proportion of licensed drivers that are under 21.

I keep putting the values in Excel and my answers are wrong.

Answer 1

Hey!

firstly check you have data analysis add-in in your excel to do this, hence with your data, select the column 2 as the range of "y" and select column 3 as the range for x.

the following will the excel output

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.946472519
R Square	0.895810229
Adjusted R Square	0.863552165
Standard Error	3.804246998
Observations	32
ANOVA
	df	SS	MS	F	Significance F
Regression	1	3857.358848	3857.358848	266.5340078	1.80762E-16
Residual	31	448.6411519	14.47229522
Total	32	4306
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	0	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
X Variable 1	5.188255281	0.317793509	16.32586928	8.9417E-17	4.540111147	5.836399415	4.540111147	5.836399415

now to answer the questions

10) 5.188255281 is the number of increase in accidents for 1% increase in the number of drivers under 21

11) Standard error is defined as the standard deviation of the statistic and here statistic is the coefficient so hence standard deviation of your coefficient is 0.317793509

12) intercept is 0. i.e no accidents occur naturally.

13) The % of variation in "Y" explained by the given "X" is given by Rsquare value. hence here 89.581% of variation in "Y" is being explained by "X"

Y here is the no of fatal accidents every 1000 liecenses

X is the percentage of drivers under age 21.