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)
34	123,556.00
17	92,425.00
42	250,908.00
35	204,540.00
19	77,897.00
43	197,012.00
51	195,126.00
50	177,100.00
22	83,230.00
58	140,012.00
48	265,296.00
35	189,420.00
39	235,872.00
39	230,724.00
59	238,655.00
40	138,560.00
60	259,680.00
22	93,208.00
33	91,212.00
36	153,216.00
28	77,308.00
22	56,496.00
28	106,652.00
44	242,748.00
54	195,858.00
30	178,560.00
28	190,876.00
16	98,528.00
52	169,572.00
22	79,420.00
28	167,928.00
35	215,705.00
50	146,350.00

1. Use Data > Data Analysis > Correlation to compute the correlation check the Labels checkbox. Show work in excel.

2. Use the excel function =CORREL to compute the correlation. If answer for #1 and 2 do not agree, there is an error. Show work in excel.

Answer 1

Solution:

We are given data which show respondents age and answer to the question “How many minutes do you browse online retailers per week?”

1. Use Data > Data Analysis > Correlation to compute the correlation check the Labels checkbox. Show work in excel.

Click on Data Analysis and select Correlation

Select both columns at a time with heading and thus select check box of Label

for output select any blank cell and click on OK

thus correlation coefficient = r = 0.6857968

r = 0.6858

Age	Time
34	1,23,556.00
17	92,425.00
42	2,50,908.00
35	2,04,540.00
19	77,897.00
43	1,97,012.00
51	1,95,126.00
50	1,77,100.00
22	83,230.00
58	1,40,012.00
48	2,65,296.00
35	1,89,420.00
39	2,35,872.00
39	2,30,724.00
59	2,38,655.00
40	1,38,560.00
60	2,59,680.00
22	93,208.00
33	91,212.00
36	1,53,216.00
28	77,308.00
22	56,496.00
28	1,06,652.00
44	2,42,748.00
54	1,95,858.00
30	1,78,560.00
28	1,90,876.00
16	98,528.00
52	1,69,572.00
22	79,420.00
28	1,67,928.00
35	2,15,705.00
50	1,46,350.00

	Age	Time
Age	1
Time	0.68579684	1

2. Use the excel function =CORREL to compute the correlation. If answer for #1 and 2 do not agree, there is an error. Show work in excel.

=CORREL( select Age column numbers , select Time column numbers) and press Enter

r = 0.6857968

r = 0.6858

Thus by using Excel command =CORREL we get same answer as that of by using Data Analysis -> Correlation.