Question

A government lobby that supports government regulation of the snack industry is looking into snack foods. Looking at a sample of 20 unhealthy snacks (e.g., sweets) they recorded the typical portion size in ounces and the calories per ounce of each snack. This data has been included below. Use Excel to calculate the correlation coefficient r between the two data sets. Round your answer to two decimal places.

oz/portion   cal/oz
0.91   274.48
0.92   134.03
0.95   146.06
0.93   128.22
2.01   84.79
2.51   108.79
2.01   117.65
0.77   104.97
0.90   118.18
1.13   109.69
3.72   49.35
3.28   113.04
3.34   77.55
1.00   80.46
1.10   155.36
1.59   110.94
4.08   36.57
1.40   136.39
2.71   108.94
1.21   145.03

r=?

Answer 1

using excel direct

x	y
0.91	274.48
0.92	134.03
0.95	146.06
0.93	128.22
2.01	84.79
2.51	108.79
2.01	117.65
0.77	104.97
0.9	118.18
1.13	109.69
3.72	49
3.28	113.04
3.34	77.55
1	80.46
1.1	155.36
1.59	110.94
4.08	36.57
1.4	136.39
2.71	108.94
1.21	145.03

command is =CORREL(array1,array2)

correlation coefficient is r = -0.60475 = -0.60