SOLVE THE FOLLOWING PROBLEMS IN TWO WAYS:

(a) USING CALCULATOR (by hand) OR EXCEL BASIC COMPUTATIONS

(b) USING MS EXCEL REGRESSION ANALYSIS

Write out the regression equation.

What is the correlation coefficient?

What is r-square (coefficient of determination)?

Draw the conclusion (very important)

Problem 2:

A researcher is interested in determining whether there is a relationship between shelf space and number of books sold for her bookstore.

Shelf Space in feet(X)

Books Sold(Y)

7.0

280

3.5

140

4.0

170

4.2

200

4.8

215

3.9

190

4.9

240

7.5

295

3.0

125

5.9

265

5.0

200

Here is an example given

Problem (with solution):

A researcher is interested in determining whether there is a relationship between number of packs of cigarettes smoked per day and longevity (in years). n=10.

# packs of cigarettes smoked

(X)

Longevity (Y)

0

80

0

70

1

72

1

70

2

68

2

65

3

69

3

60

4

58

4

55

Answer:

A:

1- You are given data for Xi (independent variable) and Yi (dependent variable).

X = 20; Y = 667; XY = 1247; X2 = 60; Y2 = 44,983 (done with basic excel computations)

2- Calculate the correlation coefficient, r:

r = EMBED Equation.3 -1 r 1

r = (10*1247-20*667)/[squareroot(10*60-400)(10*44983-444889)]=

-870/squareroot(200*4941)= -870/994.08 = -.875

3- Calculate the coefficient of determination: r2 = EMBED Equation.3 = (r)2 = .766

0 r2 1

This is the proportion of the variation in the dependent variable (Yi) explained by the independent variable (Xi)

4- Calculate the regression coefficient b1 (the slope):

b1 = EMBED Equation.3 = -870/(10*60 - 400) = - 4.35

Note that you have already calculated the numerator and the denominator for parts of r. Other than a single division operation, no new calculations are required.

BTW, r and b1 are related. If a correlation is negative, the slope term must be negative; a positive slope means a positive correlation.

5- Calculate the regression coefficient b0 (the Y-intercept, or constant):

b0 = EMBED Equation.3 = 66.7 - (-4.35*2) = 75.4

6- The regression equation (a straight line) is:

EMBED Equation.3 = b0 + b1Xi Longevity = 75.4 - 4.35 (Packs of cigarettes smoked)

B. Excel regression analysis

SHAPE \* MERGEFORMAT

Conclusion (VERY IMPORTANT)

The regression is statistically significant (F-value = 26.18, p =.0009); Explain why; Check file "regression Interpretation"

r = -.875; it shows a strong negative relationship; the higher the cigarettes intake the lower the longevity.

r2 = 76.6% the coefficient of determination; 76.6% of the variation in the dependent variable Longevity is explained by the independent variable number of cigarette packs and 23.4% is not.

The regression equation is:

EMBED Equation.3 = b0 + b1Xi Longevity = 75.4 - 4.35* (Packs of cigarettes smoked)

Every pack a person smokes per day results on average in a loss of 4.35 years of life.

Someone who does not smoke ( solve for x= 0) is expected to live in average 75.4 years.