ScatterPlot
help4days
ScatterplotAnalysis.xlsxData
| FILL IN ALL CELLS THAT ARE HIGHLIGHTED IN YELLOW | ||||
| Please remember to save this file with your last name in the file name. For example: | ||||
| Name: | ||||
| Annual Amount Spent on Organic Food | Age | Annual Income | Number of People in Household | Gender (0 = Male; 1 = Female) |
| 7348 | 77 | 109688 | 3 | 1 |
| 11598 | 47 | 109981 | 5 | 1 |
| 9224 | 23 | 112139 | 4 | 1 |
| 12991 | 38 | 113420 | 5 | 1 |
| 16556 | 58 | 114101 | 5 | 0 |
| 11515 | 44 | 115100 | 5 | 0 |
| 10469 | 34 | 116330 | 5 | 0 |
| 17933 | 75 | 116339 | 6 | 0 |
| 18173 | 32 | 117907 | 7 | 0 |
| 12305 | 39 | 119071 | 5 | 1 |
| 9080 | 65 | 58603 | 5 | 1 |
| 9113 | 48 | 58623 | 4 | 1 |
| 6185 | 48 | 61579 | 2 | 1 |
| 6470 | 49 | 62180 | 2 | 0 |
| 6000 | 57 | 62202 | 5 | 1 |
| 6760 | 71 | 68041 | 2 | 0 |
| 8579 | 47 | 68407 | 4 | 1 |
| 7393 | 47 | 69618 | 3 | 1 |
| 8161 | 28 | 73079 | 4 | 0 |
| 10800 | 63 | 75900 | 5 | 1 |
| 6160 | 24 | 77129 | 3 | 1 |
| 10800 | 66 | 79618 | 6 | 0 |
| 8543 | 24 | 81131 | 4 | 0 |
| 17666 | 38 | 86246 | 6 | 1 |
| 12644 | 54 | 89167 | 5 | 1 |
| 14308 | 28 | 89576 | 5 | 1 |
| 9737 | 58 | 92296 | 4 | 0 |
| 13301 | 27 | 93614 | 5 | 1 |
| 18106 | 48 | 93954 | 6 | 1 |
| 11468 | 26 | 95937 | 5 | 1 |
| 9547 | 52 | 100846 | 4 | 0 |
| 7812 | 29 | 103276 | 2 | 1 |
| 15521 | 75 | 104112 | 5 | 1 |
| 7598 | 45 | 105119 | 4 | 0 |
| 7783 | 74 | 105925 | 3 | 1 |
| 17737 | 56 | 106084 | 6 | 0 |
| 7824 | 30 | 108616 | 3 | 1 |
| 6552 | 57 | 109038 | 2 | 0 |
| 11232 | 41 | 109585 | 5 | 1 |
| 6540 | 23 | 37834 | 5 | 0 |
| 4200 | 28 | 38940 | 5 | 0 |
| 7225 | 23 | 42145 | 5 | 0 |
| 5370 | 45 | 48677 | 5 | 1 |
| 4476 | 33 | 48997 | 4 | 1 |
| 2800 | 42 | 49058 | 1 | 1 |
| 7839 | 39 | 49609 | 4 | 1 |
| 3472 | 60 | 53279 | 1 | 0 |
| 8854 | 57 | 53917 | 5 | 0 |
| 8900 | 41 | 54716 | 5 | 0 |
| 12791 | 67 | 126306 | 5 | 0 |
| 12712 | 73 | 130893 | 5 | 1 |
| 13321 | 57 | 134488 | 5 | 1 |
| 8802 | 64 | 135711 | 4 | 0 |
| 14369 | 24 | 139701 | 5 | 1 |
| 7908 | 25 | 142014 | 4 | 1 |
| 17840 | 34 | 142857 | 6 | 0 |
| 15107 | 78 | 143182 | 5 | 1 |
| 12070 | 34 | 150987 | 5 | 1 |
| 6389 | 34 | 152041 | 2 | 1 |
| 6606 | 41 | 154702 | 3 | 0 |
| 6291 | 62 | 155552 | 1 | 1 |
| 7425 | 57 | 157329 | 3 | 0 |
| 11436 | 23 | 163794 | 5 | 1 |
| 7612 | 78 | 164108 | 4 | 0 |
| 7515 | 36 | 165851 | 4 | 0 |
| 13115 | 44 | 172497 | 5 | 1 |
| 11870 | 75 | 174458 | 5 | 0 |
| 8450 | 70 | 177517 | 4 | 1 |
| 16324 | 38 | 183779 | 5 | 0 |
| 9331 | 35 | 185111 | 4 | 0 |
| 9184 | 65 | 186467 | 4 | 0 |
| 16803 | 68 | 189137 | 6 | 0 |
| 10709 | 48 | 194351 | 5 | 1 |
| 14456 | 24 | 194380 | 5 | 0 |
| 16634 | 46 | 197358 | 5 | 0 |
| 12227 | 43 | 197400 | 5 | 1 |
| 13476 | 58 | 198650 | 5 | 0 |
| 14554 | 66 | 202859 | 5 | 1 |
| 9393 | 68 | 203591 | 4 | 1 |
| 14594 | 74 | 206216 | 5 | 1 |
| 6628 | 32 | 207679 | 2 | 0 |
| 11240 | 61 | 210498 | 5 | 0 |
| 13101 | 42 | 210678 | 5 | 1 |
| 14034 | 60 | 211249 | 5 | 0 |
| 17837 | 64 | 211961 | 6 | 1 |
| 7849 | 53 | 212851 | 2 | 1 |
| 10578 | 62 | 213035 | 5 | 1 |
| 11325 | 78 | 214457 | 5 | 0 |
| 7105 | 44 | 215442 | 2 | 0 |
| 16460 | 58 | 220178 | 5 | 1 |
| 8390 | 27 | 220403 | 3 | 1 |
| 14956 | 68 | 220893 | 5 | 1 |
| 10903 | 21 | 221223 | 4 | 1 |
| 12054 | 70 | 221498 | 5 | 1 |
| 11697 | 38 | 222618 | 5 | 1 |
| 12781 | 25 | 229072 | 5 | 1 |
| 17456 | 30 | 229685 | 6 | 1 |
| 12835 | 70 | 230228 | 5 | 1 |
| 13403 | 37 | 235617 | 5 | 0 |
| 15051 | 40 | 238087 | 5 | 0 |
| 14225 | 29 | 240768 | 5 | 0 |
| 11196 | 54 | 242529 | 4 | 0 |
| 11475 | 52 | 243765 | 5 | 1 |
| 5605 | 65 | 244625 | 2 | 0 |
| 9890 | 72 | 245208 | 4 | 1 |
| 13227 | 40 | 247648 | 5 | 0 |
| 11200 | 36 | 249805 | 4 | 1 |
| 9600 | 43 | 252033 | 4 | 0 |
| 15703 | 38 | 252812 | 5 | 1 |
| 6486 | 73 | 257143 | 1 | 1 |
| 9430 | 41 | 258167 | 4 | 1 |
| 7755 | 35 | 258640 | 2 | 1 |
| 8100 | 21 | 261020 | 3 | 1 |
| 14821 | 59 | 266223 | 5 | 1 |
| 10650 | 56 | 266269 | 5 | 1 |
| 12589 | 42 | 267565 | 5 | 1 |
| 11600 | 46 | 268380 | 4 | 1 |
| 13000 | 34 | 269431 | 4 | 1 |
| 17065 | 70 | 269839 | 6 | 0 |
| 16500 | 55 | 270441 | 5 | 0 |
| 8600 | 38 | 272795 | 2 | 0 |
| 11900 | 51 | 274846 | 4 | 1 |
| 16723 | 66 | 276250 | 5 | 0 |
| 16759 | 43 | 277231 | 5 | 0 |
Questions 1 -3
| FILL IN ALL CELLS THAT ARE HIGHLIGHTED IN YELLOW | |
| QUESTION 1: Create a scatterplot in Excel with “Annual Amount Spent on Organic Food” on the y (vertical) axis and “Age” on the x (horizontal) axis. | |
| QUESTION 2: Insert a trendline. | |
| QUESTION 3: What does the trendline indicate about the relationship between these two variables? | |
| Age | Annual Amount Spent on Organic Food |
| 77 | 7348 |
| 47 | 11598 |
| 23 | 9224 |
| 38 | 12991 |
| 58 | 16556 |
| 44 | 11515 |
| 34 | 10469 |
| 75 | 17933 |
| 32 | 18173 |
| 39 | 12305 |
| 65 | 9080 |
| 48 | 9113 |
| 48 | 6185 |
| 49 | 6470 |
| 57 | 6000 |
| 71 | 6760 |
| 47 | 8579 |
| 47 | 7393 |
| 28 | 8161 |
| 63 | 10800 |
| 24 | 6160 |
| 66 | 10800 |
| 24 | 8543 |
| 38 | 17666 |
| 54 | 12644 |
| 28 | 14308 |
| 58 | 9737 |
| 27 | 13301 |
| 48 | 18106 |
| 26 | 11468 |
| 52 | 9547 |
| 29 | 7812 |
| 75 | 15521 |
| 45 | 7598 |
| 74 | 7783 |
| 56 | 17737 |
| 30 | 7824 |
| 57 | 6552 |
| 41 | 11232 |
| 23 | 6540 |
| 28 | 4200 |
| 23 | 7225 |
| 45 | 5370 |
| 33 | 4476 |
| 42 | 2800 |
| 39 | 7839 |
| 60 | 3472 |
| 57 | 8854 |
| 41 | 8900 |
| 67 | 12791 |
| 73 | 12712 |
| 57 | 13321 |
| 64 | 8802 |
| 24 | 14369 |
| 25 | 7908 |
| 34 | 17840 |
| 78 | 15107 |
| 34 | 12070 |
| 34 | 6389 |
| 41 | 6606 |
| 62 | 6291 |
| 57 | 7425 |
| 23 | 11436 |
| 78 | 7612 |
| 36 | 7515 |
| 44 | 13115 |
| 75 | 11870 |
| 70 | 8450 |
| 38 | 16324 |
| 35 | 9331 |
| 65 | 9184 |
| 68 | 16803 |
| 48 | 10709 |
| 24 | 14456 |
| 46 | 16634 |
| 43 | 12227 |
| 58 | 13476 |
| 66 | 14554 |
| 68 | 9393 |
| 74 | 14594 |
| 32 | 6628 |
| 61 | 11240 |
| 42 | 13101 |
| 60 | 14034 |
| 64 | 17837 |
| 53 | 7849 |
| 62 | 10578 |
| 78 | 11325 |
| 44 | 7105 |
| 58 | 16460 |
| 27 | 8390 |
| 68 | 14956 |
| 21 | 10903 |
| 70 | 12054 |
| 38 | 11697 |
| 25 | 12781 |
| 30 | 17456 |
| 70 | 12835 |
| 37 | 13403 |
| 40 | 15051 |
| 29 | 14225 |
| 54 | 11196 |
| 52 | 11475 |
| 65 | 5605 |
| 72 | 9890 |
| 40 | 13227 |
| 36 | 11200 |
| 43 | 9600 |
| 38 | 15703 |
| 73 | 6486 |
| 41 | 9430 |
| 35 | 7755 |
| 21 | 8100 |
| 59 | 14821 |
| 56 | 10650 |
| 42 | 12589 |
| 46 | 11600 |
| 34 | 13000 |
| 70 | 17065 |
| 55 | 16500 |
| 38 | 8600 |
| 51 | 11900 |
| 66 | 16723 |
| 43 | 16759 |
Recall: Click on the "Insert" tab and Choose the Scatterplot Chart.
Recall: You can insert a trendline by "right-clicking" on the coordinates on the scatterplot and choosing the "Add Trendline" option.
Questions 4-8
| FILL IN ALL CELLS THAT ARE HIGHLIGHTED IN YELLOW | |||
| QUESTION 4: Calculate the correlation coefficient for these two variables using the =CORRELATION() formula in Excel. | |||
| QUESTION 5: Interpret the correlation coefficient | |||
| QUESTION 6: Does the correlation coefficient agree with the slope of the best fit line? Explain. | |||
| 5. | |||
| QUESTION 7: Add the equation for the best fit line on the chart. | |||
| QUESTION 8: Does this equation match the linear regression equation from the Case for this Module? Explain. |
Recall: To add the equation to the chart, right- click on the trendline and choose "Format Trendline." Then, choose the box next to: "Display Equation on Chart."
Note: Keep in mind the equation is for the trendline, so it will take on the normal structure of an equation for a line: y = mx + b, where m is the slope, and b is the y-intercept. The regression equation will take on the following structure: y = α + bx, where b is the slope and α is the y-intercept.
