Analysis

help4days
3Info.xlsx
Home>Mathematics homework help>Numerical analysis homework help>Analysis

Data

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.

Scatterplot

Annual Amount Spent on Organic Food

77 47 23 38 58 44 34 75 32 39 65 48 48 49 57 71 47 47 28 63 24 66 24 38 54 28 58 27 48 26 52 29 75 45 74 56 30 57 41 23 28 23 45 33 42 39 60 57 41 67 73 57 64 24 25 34 78 34 34 41 62 57 23 78 36 44 75 70 38 35 65 68 48 24 46 43 58 66 68 74 32 61 42 60 64 53 62 78 44 58 27 68 21 70 38 25 30 70 37 40 29 54 52 65 72 40 36 43 38 73 41 35 21 59 56 42 46 34 70 55 38 51 66 43 7348 11598 9224 12991 16556 11515 10469 17933 18173 12305 9080 9113 6185 6470 6000 6760 8579 7393 8161 10800 6160 10800 8543 17666 12644 14308 9737 13301 18106 11468 9547 7812 15521 7598 7783 17737 7824 6552 11232 6540 4200 7225 5370 4476 2800 7839 3472 8854 8900 12791 12712 13321 8802 14369 7908 17840 15107 12070 6389 6606 6291 7425 11436 7612 7515 13115 11870 8450 16324 9331 9184 16803 10709 14456 16634 12227 13476 14554 9393 14594 6628 11240 13101 14034 17837 7849 10578 11325 7105 16460 8390 14956 10903 12054 11697 12781 17456 12835 13403 15051 14225 11196 11475 5605 9890 13227 11200 9600 15703 6486 9430 7755 8100 14821 10650 12589 11600 13000 17065 16500 8600 11900 16723 16759

Age

Annual Amount Spent on Organic Food

Recall: You can insert a trendline by "right-clicking" on the coordinates on the scatterplot and choosing the "Add Trendline" option.

Scatterplot

Annual Amount Spent on Organic Food

77 47 23 38 58 44 34 75 32 39 65 48 48 49 57 71 47 47 28 63 24 66 24 38 54 28 58 27 48 26 52 29 75 45 74 56 30 57 41 23 28 23 45 33 42 39 60 57 41 67 73 57 64 24 25 34 78 34 34 41 62 57 23 78 36 44 75 70 38 35 65 68 48 24 46 43 58 66 68 74 32 61 42 60 64 53 62 78 44 58 27 68 21 70 38 25 30 70 37 40 29 54 52 65 72 40 36 43 38 73 41 35 21 59 56 42 46 34 70 55 38 51 66 43 7348 11598 9224 12991 16556 11515 10469 17933 18173 12305 9080 9113 6185 6470 6000 6760 8579 7393 8161 10800 6160 10800 8543 17666 12644 14308 9737 13301 18106 11468 9547 7812 15521 7598 7783 17737 7824 6552 11232 6540 4200 7225 5370 4476 2800 7839 3472 8854 8900 12791 12712 13321 8802 14369 7908 17840 15107 12070 6389 6606 6291 7425 11436 7612 7515 13115 11870 8450 16324 9331 9184 16803 10709 14456 16634 12227 13476 14554 9393 14594 6628 11240 13101 14034 17837 7849 10578 11325 7105 16460 8390 14956 10903 12054 11697 12781 17456 12835 13403 15051 14225 11196 11475 5605 9890 13227 11200 9600 15703 6486 9430 7755 8100 14821 10650 12589 11600 13000 17065 16500 8600 11900 16723 16759

Age

Annual Amount Spent on Organic Food

The trendline indicate that there is linear positive relationship exist between Annual Amount Spent on Organic Food” on the y (vertical) axis and “Age” on the x (horizontal) axis..

Scatterplot

Annual Amount Spent on Organic Food

77 47 23 38 58 44 34 75 32 39 65 48 48 49 57 71 47 47 28 63 24 66 24 38 54 28 58 27 48 26 52 29 75 45 74 56 30 57 41 23 28 23 45 33 42 39 60 57 41 67 73 57 64 24 25 34 78 34 34 41 62 57 23 78 36 44 75 70 38 35 65 68 48 24 46 43 58 66 68 74 32 61 42 60 64 53 62 78 44 58 27 68 21 70 38 25 30 70 37 40 29 54 52 65 72 40 36 43 38 73 41 35 21 59 56 42 46 34 70 55 38 51 66 43 7348 11598 9224 12991 16556 11515 10469 17933 18173 12305 9080 9113 6185 6470 6000 6760 8579 7393 8161 10800 6160 10800 8543 17666 12644 14308 9737 13301 18106 11468 9547 7812 15521 7598 7783 17737 7824 6552 11232 6540 4200 7225 5370 4476 2800 7839 3472 8854 8900 12791 12712 13321 8802 14369 7908 17840 15107 12070 6389 6606 6291 7425 11436 7612 7515 13115 11870 8450 16324 9331 9184 16803 10709 14456 16634 12227 13476 14554 9393 14594 6628 11240 13101 14034 17837 7849 10578 11325 7105 16460 8390 14956 10903 12054 11697 12781 17456 12835 13403 15051 14225 11196 11475 5605 9890 13227 11200 9600 15703 6486 9430 7755 8100 14821 10650 12589 11600 13000 17065 16500 8600 11900 16723 16759

Age

Annual Amount Spent on Organic Food

Scatterplot

Annual Amount Spent on Organic Food

77 47 23 38 58 44 34 75 32 39 65 48 48 49 57 71 47 47 28 63 24 66 24 38 54 28 58 27 48 26 52 29 75 45 74 56 30 57 41 23 28 23 45 33 42 39 60 57 41 67 73 57 64 24 25 34 78 34 34 41 62 57 23 78 36 44 75 70 38 35 65 68 48 24 46 43 58 66 68 74 32 61 42 60 64 53 62 78 44 58 27 68 21 70 38 25 30 70 37 40 29 54 52 65 72 40 36 43 38 73 41 35 21 59 56 42 46 34 70 55 38 51 66 43 7348 11598 9224 12991 16556 11515 10469 17933 18173 12305 9080 9113 6185 6470 6000 6760 8579 7393 8161 10800 6160 10800 8543 17666 12644 14308 9737 13301 18106 11468 9547 7812 15521 7598 7783 17737 7824 6552 11232 6540 4200 7225 5370 4476 2800 7839 3472 8854 8900 12791 12712 13321 8802 14369 7908 17840 15107 12070 6389 6606 6291 7425 11436 7612 7515 13115 11870 8450 16324 9331 9184 16803 10709 14456 16634 12227 13476 14554 9393 14594 6628 11240 13101 14034 17837 7849 10578 11325 7105 16460 8390 14956 10903 12054 11697 12781 17456 12835 13403 15051 14225 11196 11475 5605 9890 13227 11200 9600 15703 6486 9430 7755 8100 14821 10650 12589 11600 13000 17065 16500 8600 11900 16723 16759

Age

Annual Amount Spent on Organic Food

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. 0.1149
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. The equation is: y = 26.293x + 9778.3

The correlation coefficient indicates that there is moderate uphill (positive) relationship between two variables on a scatterplot

Scatterplot

Annual Amount Spent on Organic Food

77 47 23 38 58 44 34 75 32 39 65 48 48 49 57 71 47 47 28 63 24 66 24 38 54 28 58 27 48 26 52 29 75 45 74 56 30 57 41 23 28 23 45 33 42 39 60 57 41 67 73 57 64 24 25 34 78 34 34 41 62 57 23 78 36 44 75 70 38 35 65 68 48 24 46 43 58 66 68 74 32 61 42 60 64 53 62 78 44 58 27 68 21 70 38 25 30 70 37 40 29 54 52 65 72 40 36 43 38 73 41 35 21 59 56 42 46 34 70 55 38 51 66 43 7348 11598 9224 12991 16556 11515 10469 17933 18173 12305 9080 9113 6185 6470 6000 6760 8579 7393 8161 10800 6160 10800 8543 17666 12644 14308 9737 13301 18106 11468 9547 7812 15521 7598 7783 17737 7824 6552 11232 6540 4200 7225 5370 4476 2800 7839 3472 8854 8900 12791 12712 13321 8802 14369 7908 17840 15107 12070 6389 6606 6291 7425 11436 7612 7515 13115 11870 8450 16324 9331 9184 16803 10709 14456 16634 12227 13476 14554 9393 14594 6628 11240 13101 14034 17837 7849 10578 11325 7105 16460 8390 14956 10903 12054 11697 12781 17456 12835 13403 15051 14225 11196 11475 5605 9890 13227 11200 9600 15703 6486 9430 7755 8100 14821 10650 12589 11600 13000 17065 16500 8600 11900 16723 16759

Age

Annual Amount Spent on Organic Food

If we increase the age by 1 year then Annual Amount Spent on Organic Food will increase by 26.293 The units for slope are the units of the Y variable per units of the X variable. It’s a ratio of change in Y per change in X. The y-intercept is the place where the regression line y = 26.293x + 9778.3  crosses the y-axis (Annual Amount Spent on Organic Food )(where x  (Age)= 0), and is denoted by b.

Scatterplot

Annual Amount Spent on Organic Food

77 47 23 38 58 44 34 75 32 39 65 48 48 49 57 71 47 47 28 63 24 66 24 38 54 28 58 27 48 26 52 29 75 45 74 56 30 57 41 23 28 23 45 33 42 39 60 57 41 67 73 57 64 24 25 34 78 34 34 41 62 57 23 78 36 44 75 70 38 35 65 68 48 24 46 43 58 66 68 74 32 61 42 60 64 53 62 78 44 58 27 68 21 70 38 25 30 70 37 40 29 54 52 65 72 40 36 43 38 73 41 35 21 59 56 42 46 34 70 55 38 51 66 43 7348 11598 9224 12991 16556 11515 10469 17933 18173 12305 9080 9113 6185 6470 6000 6760 8579 7393 8161 10800 6160 10800 8543 17666 12644 14308 9737 13301 18106 11468 9547 7812 15521 7598 7783 17737 7824 6552 11232 6540 4200 7225 5370 4476 2800 7839 3472 8854 8900 12791 12712 13321 8802 14369 7908 17840 15107 12070 6389 6606 6291 7425 11436 7612 7515 13115 11870 8450 16324 9331 9184 16803 10709 14456 16634 12227 13476 14554 9393 14594 6628 11240 13101 14034 17837 7849 10578 11325 7105 16460 8390 14956 10903 12054 11697 12781 17456 12835 13403 15051 14225 11196 11475 5605 9890 13227 11200 9600 15703 6486 9430 7755 8100 14821 10650 12589 11600 13000 17065 16500 8600 11900 16723 16759

Age

Annual Amount Spent on Organic Food

Yes becasue correlation coefficient is positive and slope is also postive

Scatterplot

Annual Amount Spent on Organic Food

77 47 23 38 58 44 34 75 32 39 65 48 48 49 57 71 47 47 28 63 24 66 24 38 54 28 58 27 48 26 52 29 75 45 74 56 30 57 41 23 28 23 45 33 42 39 60 57 41 67 73 57 64 24 25 34 78 34 34 41 62 57 23 78 36 44 75 70 38 35 65 68 48 24 46 43 58 66 68 74 32 61 42 60 64 53 62 78 44 58 27 68 21 70 38 25 30 70 37 40 29 54 52 65 72 40 36 43 38 73 41 35 21 59 56 42 46 34 70 55 38 51 66 43 7348 11598 9224 12991 16556 11515 10469 17933 18173 12305 9080 9113 6185 6470 6000 6760 8579 7393 8161 10800 6160 10800 8543 17666 12644 14308 9737 13301 18106 11468 9547 7812 15521 7598 7783 17737 7824 6552 11232 6540 4200 7225 5370 4476 2800 7839 3472 8854 8900 12791 12712 13321 8802 14369 7908 17840 15107 12070 6389 6606 6291 7425 11436 7612 7515 13115 11870 8450 16324 9331 9184 16803 10709 14456 16634 12227 13476 14554 9393 14594 6628 11240 13101 14034 17837 7849 10578 11325 7105 16460 8390 14956 10903 12054 11697 12781 17456 12835 13403 15051 14225 11196 11475 5605 9890 13227 11200 9600 15703 6486 9430 7755 8100 14821 10650 12589 11600 13000 17065 16500 8600 11900 16723 16759

Age

Annual Amount Spent on Organic Food

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."

Scatterplot

Annual Amount Spent on Organic Food

77 47 23 38 58 44 34 75 32 39 65 48 48 49 57 71 47 47 28 63 24 66 24 38 54 28 58 27 48 26 52 29 75 45 74 56 30 57 41 23 28 23 45 33 42 39 60 57 41 67 73 57 64 24 25 34 78 34 34 41 62 57 23 78 36 44 75 70 38 35 65 68 48 24 46 43 58 66 68 74 32 61 42 60 64 53 62 78 44 58 27 68 21 70 38 25 30 70 37 40 29 54 52 65 72 40 36 43 38 73 41 35 21 59 56 42 46 34 70 55 38 51 66 43 7348 11598 9224 12991 16556 11515 10469 17933 18173 12305 9080 9113 6185 6470 6000 6760 8579 7393 8161 10800 6160 10800 8543 17666 12644 14308 9737 13301 18106 11468 9547 7812 15521 7598 7783 17737 7824 6552 11232 6540 4200 7225 5370 4476 2800 7839 3472 8854 8900 12791 12712 13321 8802 14369 7908 17840 15107 12070 6389 6606 6291 7425 11436 7612 7515 13115 11870 8450 16324 9331 9184 16803 10709 14456 16634 12227 13476 14554 9393 14594 6628 11240 13101 14034 17837 7849 10578 11325 7105 16460 8390 14956 10903 12054 11697 12781 17456 12835 13403 15051 14225 11196 11475 5605 9890 13227 11200 9600 15703 6486 9430 7755 8100 14821 10650 12589 11600 13000 17065 16500 8600 11900 16723 16759

Age

Annual Amount Spent on Organic Food

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.

Scatterplot

Annual Amount Spent on Organic Food

77 47 23 38 58 44 34 75 32 39 65 48 48 49 57 71 47 47 28 63 24 66 24 38 54 28 58 27 48 26 52 29 75 45 74 56 30 57 41 23 28 23 45 33 42 39 60 57 41 67 73 57 64 24 25 34 78 34 34 41 62 57 23 78 36 44 75 70 38 35 65 68 48 24 46 43 58 66 68 74 32 61 42 60 64 53 62 78 44 58 27 68 21 70 38 25 30 70 37 40 29 54 52 65 72 40 36 43 38 73 41 35 21 59 56 42 46 34 70 55 38 51 66 43 7348 11598 9224 12991 16556 11515 10469 17933 18173 12305 9080 9113 6185 6470 6000 6760 8579 7393 8161 10800 6160 10800 8543 17666 12644 14308 9737 13301 18106 11468 9547 7812 15521 7598 7783 17737 7824 6552 11232 6540 4200 7225 5370 4476 2800 7839 3472 8854 8900 12791 12712 13321 8802 14369 7908 17840 15107 12070 6389 6606 6291 7425 11436 7612 7515 13115 11870 8450 16324 9331 9184 16803 10709 14456 16634 12227 13476 14554 9393 14594 6628 11240 13101 14034 17837 7849 10578 11325 7105 16460 8390 14956 10903 12054 11697 12781 17456 12835 13403 15051 14225 11196 11475 5605 9890 13227 11200 9600 15703 6486 9430 7755 8100 14821 10650 12589 11600 13000 17065 16500 8600 11900 16723 16759

Age

Annual Amount Spent on Organic Food

Scatterplot

Annual Amount Spent on Organic Food

77 47 23 38 58 44 34 75 32 39 65 48 48 49 57 71 47 47 28 63 24 66 24 38 54 28 58 27 48 26 52 29 75 45 74 56 30 57 41 23 28 23 45 33 42 39 60 57 41 67 73 57 64 24 25 34 78 34 34 41 62 57 23 78 36 44 75 70 38 35 65 68 48 24 46 43 58 66 68 74 32 61 42 60 64 53 62 78 44 58 27 68 21 70 38 25 30 70 37 40 29 54 52 65 72 40 36 43 38 73 41 35 21 59 56 42 46 34 70 55 38 51 66 43 7348 11598 9224 12991 16556 11515 10469 17933 18173 12305 9080 9113 6185 6470 6000 6760 8579 7393 8161 10800 6160 10800 8543 17666 12644 14308 9737 13301 18106 11468 9547 7812 15521 7598 7783 17737 7824 6552 11232 6540 4200 7225 5370 4476 2800 7839 3472 8854 8900 12791 12712 13321 8802 14369 7908 17840 15107 12070 6389 6606 6291 7425 11436 7612 7515 13115 11870 8450 16324 9331 9184 16803 10709 14456 16634 12227 13476 14554 9393 14594 6628 11240 13101 14034 17837 7849 10578 11325 7105 16460 8390 14956 10903 12054 11697 12781 17456 12835 13403 15051 14225 11196 11475 5605 9890 13227 11200 9600 15703 6486 9430 7755 8100 14821 10650 12589 11600 13000 17065 16500 8600 11900 16723 16759

Age

Annual Amount Spent on Organic Food