Question

Studies have shown that the frequency with which shoppers browse Internet retailers is related to the frequency with which they actually purchase products and/or services online. The following data show respondents age and answer to the question “How many minutes do you browse online retailers per week?”

Age (X)	Time (Y)
65	107
42	229
50	321
22	342
40	316
18	543
22	472
17	707
21	544
35	393
39	328
62	354
52	224
27	415
26	441

1 Use Data > Data Analysis > Correlation to compute the correlation checking the Labels checkbox.

2 Use the Excel function =CORREL to compute the correlation. If answers for #1 and 2 do not agree, there is an error.

3 The strength of the correlation motivates further examination. a) Insert Scatter (X, Y) plot linked to the data on this sheet with Age on the horizontal (X) axis. b) Add to your chart: the chart name, vertical axis label, and horizontal axis label. c) Complete the chart by adding Trendline and checking boxes

4 Read directly from the chart: a) Intercept = b) Slope = c) R2 =

5 Perform Data > Data Analysis > Regression. Highlight the Y-intercept with yellow. Highlight the X variable in blue. Highlight the standard error in orange

6 Using your highlighted cells, what is the equation of the regression line?

7 Use Excel to predict the number of minutes spent by a 41-year old shopper. Enter = followed by the regression formula. Enter the intercept and slope into the formula by clicking on the cells in the regression output with the results.

Answer 1

Solution: 1 Use Data > Data Analysis > Correlation to compute the correlation checking the Labels checkbox.

Answer: The excel output is given below:

Therefore the correlation between Age (X) and Time (Y) is

2 Use the Excel function =CORREL to compute the correlation. If answers for #1 and 2 do not agree, there is an error.

Answer:

Using the =CORREL function in excel we have:

3 The strength of the correlation motivates further examination. a) Insert Scatter (X, Y) plot linked to the data on this sheet with Age on the horizontal (X) axis. b) Add to your chart: the chart name, vertical axis label, and horizontal axis label. c) Complete the chart by adding Trendline and checking boxes

Answer:

4 Read directly from the chart:

Answer: a) Intercept = 653.2 b) Slope = -7.5501 c) R² = 0.6504

5 Perform Data > Data Analysis > Regression. Highlight the Y-intercept with yellow. Highlight the X variable in blue. Highlight the standard error in orange

Answer: The excel output is given below:

6 Using your highlighted cells, what is the equation of the regression line?

Answer:

7 Use Excel to predict the number of minutes spent by a 41-year old shopper. Enter = followed by the regression formula. Enter the intercept and slope into the formula by clicking on the cells in the regression output with the results.

Answer: