MATH

profileiabepdadm
MAT144-MODULE-4MAJORASSIGNMENT_2.xlsx
Home>Mathematics homework help>MATH

Grading Sheet

Part 1
Requirements: Answer each question fully. Use Excel formulas with cell references. Answers must be recorded on the worksheet.
Possible points Points earned Comments
Five year inflation rate 10
Projection of expenses in Major Assignment 1 10
Part 1 total 20 0
Part 2
Requirements: Answer each question fully. Use Excel formulas with cell references. Answers must be recorded on the worksheet.
Possible points Points earned Comments
Descriptive Statistics 16
Interpret Descriptive Statistics 14
Proportion calculations 10
Interpretation of proportions 10
Conversions 10
Improvement level data set 5
Descriptive Statistics for improvement levels 10
Histogram 5
Standard error of the mean 5
Confidence interval 10
Discussion of the placement of 0 10
Part 2 total 105 0
Total of Worksheet 2 125 0 0%

Part 1 Inflation

Input your name below: Color Key:
Joshua Odusola-Stephen formula
text/number
You should just use the budget information from Major Assignment 1. For the CPI values, here are the instructions: Step 1: Go to the Bureau of Labor Statistics website at the link below. Step 2: Check the box next to "U.S. city average, All items - CUUR0000SA0" Step 3: Click "Retrieve Data" Use the most recent CPI value and the CPI for the same month but five years earlier to compute the 5 year inflation rate. Use this inflation rate to estimate the price of your trip five years from now.
Month Year CPI
Unadjusted CPI, all items for 5 years ago August 2015 238.31
Unadjusted CPI, all items for last month July 2020 257.21
What is the 5 year inflation rate, that is percent increase in your CPI values? 8%
If something cost $1.00 five years ago, what would it cost now? $1.08
Total budget from Major Assignment 1 $2,757.06
5 year projected budget total $2,975.72 CPI Data Link
Like Topic 4 DQ 1

Name!

https://data.bls.gov/cgi-bin/surveymost?cu

Part 2 Questions 1-3

Enter your name in cell A2 of "Part 1 - Inflation" to generate data. Part 2 – Data Analysis: Enter your name in cell A2 of "Part 1 - Inflation" to generate data. You have just completed a mission to Sierra Leone. The goal of the mission was to improve the quality of water in the wells in a certain region. You collected data on the E. coli count from each well before and after your mission. You need to write a report on the success of the mission and for that you need to perform some statistical analysis on the data. You will be looking at the data from different perspectives to determine if the water quality has improve.
Before wells were dug E.Coli per ml After wells were dug E.Coli per ml YOUR NAME: From Part 1 A1 Joshua Odusola-Stephen Original Before Data Original After Data seed
25 20 Before After 3591 120 8 8
23 33 min = 1 min = 0 Recall Topic 1 DQ 2 78 67 5020 961 20 20
25 11 max = 42 max = 38 1. Calculate descriptive statistics for your data in the table provided in the Excel spreadsheet. Use the statistics including mean, max and standard deviations of the data to decide if it appears if there has been improvement in water quality? This requires a thorough discussion of these statistics to obtain full marks. (Fill in the before (F3:F8) and after (H3:H8) tables to the left for the descriptive statistics. The data has been named before and after for your convenience in creating formulas.) 19 4 4317 33 33
18 3 mean= 24.2307692308 mean= 14.4786324786 32 23 4926 11 11
23 1 SD = 9.8122412779 SD = 11.1628185414 125 110 2611 3 3
9 25 # of wells tested = 117 # of wells tested = 117 53 41 4212 1 1
27 6 # of wells with 0 E coli = 0 # of wells with 0 E coli = 19 68 42 581 25 25
14 20 4 10 5814 6 6
27 15 Ratio Before After Answer Question 1 below: 106 79 1359 20 20
26 21 Percent Clean 0.00% 16.24% Initially, all 117 wells contained E. coli and therefore were unclean. After the mission, 20 wells had 0 amounts of e.Coli. The mean amount of e Coli per 1ml of water reduced from 24.42 per ml to 14.19 per ml. The maximum amount of e. Coli also decreased from 49 per ml to 43 per ml. The was also an increase in the stansard deviation of e. coli in the wells which captures the wells where e-coli was completely gotten rid of. Therefore, there was improvement in the quality of water. 38 6 5688 15 15
17 0 36 16 5317 21 21
1 11 Conversions Color Key: 4 14 2194 -8 0
29 0 ml oz formula 17 5 59 11 11
13 24 29.5735 1 text/number 43 9 6488 -16 0
33 21 23 3 1297 24 24
33 16 In 24 ounces 2. The water quality is “good” if the count of E coli is 0; otherwise, the water quality is still bad. Calculate the proportion of wells with “good” water to wells whose water is not good. From this measure does it appear that the quality of water improved? Explain and use the proportions that you calculated. (In G11 and H11 calculate the percent Clean for before and after.) 32 8 7490 21 21
21 18 E.coli before E. coli after 49 28 7383 16 16
36 25 17744.1 14195.28 36 18 3392 18 18
30 0 2 21 8141 25 25
2 0 33 22 6806 -1 0
3 15 Answer Question 2 below: 58 25 43 -8 0
24 0 After the mission, 17.09% of the wells had clean water compared to 0.0% of wells before the mission. This indicates that the quality of water improved on the overall. 75 59 148 15 15
13 21 82 63 4493 -1 0
23 6 80 60 1290 21 21
37 34 70 52 4143 6 6
40 17 79 50 8208 34 34
35 6 73 66 8705 17 17
23 9 3. Look at well #1 (B2 and C2) in your data. If you drank 24oz of water how many E.coli would you ingest if you drank from the well before the mission? After the mission? (In E20 and G20 calculate how many E.coli would you ingest if you drank 24 oz. of water from Well 1 before the mission and after the mission.) 76 54 7870 6 6
20 9 72 42 4327 9 9
23 5 72 55 3156 9 9
27 26 70 53 4053 5 5
25 1 84 54 5794 26 26
19 12 Nothing to answer here! 81 62 4899 1 1
17 8 69 59 2804 12 12
19 23 85 52 2153 8 8
28 14 100 69 2730 23 23
25 23 70 57 6079 14 14
32 31 74 45 4900 23 23
28 0 63 39 7213 31 31
16 31 76 60 5966 0 0
33 0 78 75 1971 31 31
6 28 87 64 7428 -4 0
26 7 71 41 13 28 28
3 14 83 67 5458 7 7
27 0 71 56 167 14 14
6 33 75 57 5716 -10 0
36 18 76 58 361 33 33
29 22 63 39 8066 18 18
33 5 70 38 6323 22 22
16 13 65 50 7572 5 5
23 1 83 59 2041 13 13
11 16 76 48 4210 1 1
33 0 78 59 935 16 16
19 5 76 49 7360 -1 0
19 20 68 47 2877 5 5
28 10 77 51 2990 20 20
27 1 75 58 5919 10 10
18 25 67 53 5704 1 1
36 12 74 45 2485 25 25
29 0 86 58 8170 12 12
9 21 85 67 6259 -14 0
27 32 72 48 556 21 21
31 30 73 65 5653 32 32
29 31 59 43 6926 30 30
37 6 72 55 6319 31 31
26 19 64 25 8280 6 6
32 23 67 48 5133 19 19
24 9 79 55 7246 23 23
39 0 64 33 4711 9 9
10 25 86 65 8564 -8 0
35 0 74 53 725 25 25
1 12 83 61 7842 -8 0
29 19 81 55 111 12 12
31 0 70 47 6456 19 19
24 23 71 54 6961 0 0
23 3 68 54 4710 23 23
28 33 76 64 4155 3 3
39 23 72 55 6084 33 33
35 5 71 55 8601 23 23
17 37 86 77 7934 5 5
40 0 86 62 2171 37 37
13 1 88 67 8716 -12 0
7 11 78 59 1337 1 1
22 3 73 45 410 11 11
8 31 84 57 3979 3 3
31 1 78 53 548 31 31
22 17 77 57 7069 1 1
20 23 88 68 3938 17 17
30 28 83 61 3219 23 23
33 13 70 32 6660 28 28
19 21 65 48 7541 13 13
26 12 74 52 2942 21 21
28 8 73 53 5243 12 12
17 37 79 58 6120 8 8
34 7 87 59 2229 37 37
17 0 79 57 7622 7 7
17 25 77 55 2363 0 0
42 0 66 52 2248 25 25
14 14 73 58 8845 -9 0
28 27 85 63 1434 14 14
31 10 77 55 6171 27 27
6 0 6960 10 10
6 10 301 -2 0
31 23 342 10 10
32 7 6843 23 23
21 0 7252 7 7
22 21 3649 -1 0
26 12 3926 21 21
30 21 5343 12 12
33 19 6764 21 21
25 30 7477 19 19
42 38 5098 30 30
42 32 8855 38 38
34 18 8836 32 32
39 1 7613 18 18
24 31 8542 1 1
30 0 4619 31 31
6504 -18 0
7637 -1 0
4294 -8 0
4411 -3 0
6632 -1 0
3325 1 1
6242 -3 0
8151 1 1
5036 -11 0
1485 -9 0
1730 2 2
8811 -6 0
7452 -11 0
4937 -14 0
5250 4 4
295 2 2
1404 -12 0
2309 -10 0
2634 -6 0
4727 3 3
5732 -21 0
6701 -6 0
4870 -18 0
3351 -21 0
1640 -17 0
6397 -11 0
3646 -11 0
9043 -7 0
3076 -24 0
9041 -6 0
3430 -19 0
1415 4 4
4948 -26 0
7889 -7 0
5550 -23 0
2227 -15 0
7976 -5 0
3909 -4 0
3766 -9 0
6147 -22 0
2036 -12 0
509 -8 0
214 -14 0
1983 0 0
5304 -2 0
9081 -4 0
5522 -15 0
7183 -8 0
2104 -3 0
6817 -11 0
2682 -23 0
5403 -11 0
5316 -5 0
6957 -20 0
5418 -14 0
7247 -13 0
9120 -21 0
3205 -22 0
9138 -11 0
19 -11 0
4396 -11 0
4701 -10 0
1162 5 5
8455 -18 0
6260 -18 0
4965 -23 0
294 -3 0
6167 5 5
7668 -6 0
3393 -10 0
3378 4 4
1447 -8 0
8456 -7 0
1497 -7 0
8778 -3 0
8707 0 0
7516 1 1
2781 5 5
1638 -7 0
6751 -6 0
5192 -2 0
1389 -13 0
378 0 0
471 -20 0
6940 -23 0
3841 -17 0
6630 -15 0
3679 -13 0
7788 -18 0
497 -5 0
2338 -23 0
2087 -6 0
5240 -16 0
2065 -11 0
9134 -4 0
727 -20 0
7488 -14 0
7737 -24 0
4938 -5 0
487 -12 0
4108 -3 0
645 -7 0
3658 -13 0
6919 -20 0
2972 -19 0
9105 -4 0
1274 0 0
6975 -8 0
2232 -10 0
2505 -9 0
4630 -11 0
9143 -6 0
3720 -15 0
5117 -6 0
906 -21 0
7907 -23 0
2364 -8 0
6657 -9 0
3486 -8 0
675 -11 0
7520 -2 0
2073 -10 0
7718 -6 0
3715 -16 0
1416 -28 0
185 -15 0
2530 -16 0
4791 -20 0
3576 -14 0
3089 -18 0
2154 -2 0
7139 -18 0
720 -16 0
4141 -5 0
8562 4 4
1079 -5 0
216 0 0
1629 -8 0
3758 -12 0
7563 -21 0
8220 0 0
6581 -7 0
7766 -23 0
4391 -6 0
1000 -7 0
441 -17 0
3078 -7 0
8687 -28 0
1884 -4 0
9069 8 8
7646 -14 0
7287 -10 0
2040 -21 0
8973 6 6
6294 -12 0
8119 -24 0
1528 -11 0
7877 -15 0
7674 5 5
2331 -6 0
7912 -17 0
6065 -12 0
7378 -10 0
8863 -15 0
7420 -3 0
1429 -20 0
2470 -20 0
6239 -15 0
4096 0 0
2769 -17 0
3762 -19 0
6855 -12 0
5128 -14 0
3545 3 3
3990 -13 0
3187 -15 0
3152 -11 0
4761 6 6
8886 -1 0
7935 -3 0
6580 -15 0
3357 -6 0
578 -26 0
1759 -11 0
8264 -18 0
7965 3 3
1270 -5 0
7683 -21 0
5324 -14 0
5541 2 2
8406 -22 0
1175 -24 0
1568 -11 0
797 -8 0
4270 -3 0
8659 -23 0
6840 -21 0
3197 -16 0
1382 -7 0
6203 -11 0
1296 -15 0
3081 -19 0
3570 -25 0
4151 -22 0
6792 -20 0
2521 -14 0
1798 -29 0
5947 -2 0
748 -20 0
8357 -12 0
5262 6 6
7343 -24 0
1300 -18 0
2373 -10 0
478 -22 0
1115 -8 0
3016 -9 0
1317 -19 0
3950 -6 0
1095 -19 0
6556 -15 0
7605 -14 0
786 -22 0
1631 -3 0
3404 -21 0
6017 -10 0
6702 -10 0
107 -7 0
7164 -25 0
881 -13 0
7746 -14 0
7931 0 0
7288 -5 0
6449 -9 0
3614 -2 0
5535 -9 0
296 -12 0
5813 -3 0
6122 6 6
1875 -3 0
6076 1 1
845 -11 0
4946 -5 0
8243 -26 0
7096 -11 0
3745 -18 0
5278 -4 0
4511 -6 0
7276 -27 0
8573 -8 0
3718 -5 0
5471 -25 0
2452 -8 0
253 -5 0
8838 -7 0
7259 -6 0
6996 -12 0
3101 -17 0
30 -19 0
7035 -3 0
784 0 0
1985 -22 0
4950 -8 0
7535 -17 0
4004 -8 0
709 -8 0
1502 -5 0
3307 -7 0
256 -7 0
3721 -4 0
354 -23 0
4719 -2 0
7148 -19 0
3713 -11 0
1770 -19 0
1731 -6 0
4048 -23 0
2093 -19 0
4178 -11 0
6599 2 2
4580 -12 0
8821 -11 0
5682 2 2
6379 0 0
6832 -26 0
4613 -14 0
7566 2 2
3103 -15 0
8848 0 0
5489 -12 0
8438 3 3
4683 -24 0
4348 -10 0
4025 -15 0
1578 -15 0
8199 -16 0
5712 -27 0
1069 -12 0
1986 -13 0
187 -1 0
2176 -21 0
3245 -5 0
2058 -7 0
5787 -19 0
1552 -5 0
3629 -14 0
7466 2 2
2459 -12 0
3600 -15 0
8013 -11 0
1946 -6 0
7267 -1 0
5580 -14 0
6089 -5 0
3130 -15 0
8655 -19 0
7548 -10 0
6289 -18 0
4418 -7 0
807 -10 0
2500 -14 0
929 -7 0
8422 -15 0
7515 -8 0
7544 -3 0
6997 -3 0
7510 -9 0
3843 1 1
6276 -24 0
2133 -2 0
6270 -11 0
3195 -6 0
1736 -9 0
7749 -2 0
2814 2 2
383 -18 0
4172 -22 0
7661 -2 0
46 0 0
4203 -7 0
6760 -7 0
8185 4 4
8190 -14 0
2719 -18 0
3440 5 5
8817 -19 0
6390 -7 0
299 -1 0
696 -17 0
8389 -26 0
8770 3 3
951 -22 0
4528 -2 0
8853 2 2
18 -16 0
9159 3 3
888 -6 0
1921 2 2
7106 -15 0
1975 -5 0
6720 -25 0
6093 4 4
2422 -4 0
5563 -17 0
4512 -21 0
2513 -19 0
3214 -13 0
2959 -1 0
6820 -25 0
6737 -21 0
7670 -3 0
3039 -19 0
1832 -20 0
9101 -1 0
1982 -10 0
895 -10 0
5268 -24 0
6281 -18 0
5834 -6 0
6991 -14 0
8572 -13 0
8481 -16 0
1658 -17 0
3211 -11 0
8076 -3 0
4553 6 6
9014 -8 0
3623 -1 0
8528 -3 0
7097 -1 0
8154 -11 0
9091 -8 0
3752 -2 0
8625 -28 0
3686 3 3
1963 -3 0
8844 5 5
6197 -2 0
2358 -19 0
7719 0 0
8124 -8 0
5229 -8 0
8598 -11 0
3879 -15 0
9076 -9 0
1821 -12 0
6462 -1 0
5899 -20 0
72 -23 0
8773 -5 0
5006 -20 0
6795 -20 0
6576 -12 0
4065 -19 0
3670 -15 0
4795 -10 0
2868 -4 0
9169 -16 0
8290 -13 0
3363 -19 0
8688 -21 0
6293 -21 0
3710 -13 0
6887 -10 0
8636 1 1
6325 -11 0
7218 -16 0
495 -23 0
2692 -1 0
3633 -17 0
6758 -25 0
8539 -11 0
564 -1 0
4237 -1 0
742 -11 0
247 -16 0
728 -11 0
2725 -7 0
2378 -4 0
4179 -6 0
1836 -15 0
8393 -16 0
8062 -7 0
7031 -20 0
1492 -13 0
5077 -24 0
7986 -21 0
2139 -15 0
5208 -7 0
7729 -17 0
6354 -4 0
6671 -17 0
1008 -6 0
8197 6 6
6066 -5 0
2615 -3 0
3504 -19 0
6661 0 0
2778 -18 0
6755 -9 0
4484 -13 0
7469 -12 0
6514 -11 0
5867 -8 0
5736 -24 0
5993 5 5
1778 -16 0
315 -7 0
7036 -18 0
5193 -17 0
5798 -2 0
4191 -5 0
8884 -16 0
8289 -22 0
8126 -6 0
4875 -13 0
7052 -24 0
2361 -8 0
2602 4 4
1219 -16 0
2952 -21 0
3473 -3 0
7562 9 9
3811 -13 0
2768 -2 0
8525 -2 0
3042 -7 0
5887 -17 0
2196 -19 0
8877 1 1
4942 -13 0
8951 0 0
1016 1 1
6781 -8 0
9054 -14 0
5715 -1 0
5124 -10 0
4253 -6 0
7082 -15 0
6223 -12 0
6928 -21 0
5965 -30 0
6734 -9 0
3615 -6 0
772 -1 0
4109 8 8
5054 -18 0
7471 -18 0
6160 -16 0
4321 3 3
4218 -18 0
8691 2 2
1176 -19 0
5977 -3 0
4610 -15 0
3511 0 0
836 -8 0
1953 -10 0
1442 -14 0
4755 -5 0
776 -4 0
3401 -21 0
1962 -12 0
4435 -3 0
2384 -17 0
3117 2 2
6370 -12 0
3839 6 6
6984 -9 0
5225 -4 0
134 -25 0
6971 -4 0
2940 -23 0
5597 -10 0
7666 0 0
3747 -4 0
4924 -9 0
2965 -15 0
5758 -19 0
2099 -9 0
3116 -7 0
1961 -5 0
26 -15 0
7743 -13 0
3876 -2 0
5021 -11 0
8726 -4 0
8739 -11 0
1852 -17 0
5561 -7 0
4866 -15 0
4059 -7 0
4732 -19 0
261 -1 0
7422 -21 0
1075 -2 0
924 -9 0
4721 -8 0
6794 -1 0
2167 -20 0
252 -15 0
4429 5 5
3446 -22 0
7755 -15 0
1752 -18 0
4917 -6 0
8790 -5 0
6583 -16 0
7412 -4 0
2845 -18 0
8654 -24 0
3139 -17 0
2476 -9 0
5177 -10 0
8630 -11 0
7387 -1 0
2684 -9 0
5049 -16 0
3770 -14 0
5439 -13 0
8116 -14 0
6645 7 7
5610 -3 0
779 -6 0
7456 -18 0
4229 -5 0
2158 -12 0
6431 -9 0
6800 -14 0
1105 -16 0
4786 -22 0
9047 -11 0
2368 -11 0
5949 -19 0
394 -6 0
6811 -22 0
3744 -28 0
869 -11 0
698 -21 0
8035 -6 0
7224 -5 0
8605 -15 0
7226 -3 0
8251 -1 0
5680 -12 0
6733 -18 0
8378 -13 0
6131 -21 0
4868 4 4
3705 -12 0
3186 -4 0
7915 -18 0
948 -10 0
473 -4 0
6586 -5 0
2295 -2 0
5112 -8 0
6377 -14 0
7186 -21 0
6159 -1 0
9084 -5 0
405 -13 0
278 2 2
8999 -17 0
1692 -14 0
6365 -15 0
138 -16 0
6263 9 9
9020 -21 0
2561 3 3
3890 -28 0
2543 -9 0
7076 -1 0
7285 -17 0
2394 6 6
1347 -9 0
7812 -19 0
5421 -21 0
2130 -27 0
2215 -13 0
928 -12 0
4013 2 2
3702 1 1
8303 -6 0
5648 -5 0
3225 4 4
5598 -1 0
2903 -13 0
7560 -9 0
4165 -5 0
4314 -17 0
871 -9 0
344 -6 0
6489 -15 0
5706 2 2
2131 -16 0
6624 -6 0
4741 -21 0
3254 6 6
5051 -1 0
3416 -20 0
3893 2 2
6598 -7 0
171 -19 0
5008 -6 0
6441 -1 0
5030 3 3
2547 -12 0
6368 -16 0
4193 -14 0
8530 -25 0
6743 -11 0
6608 -24 0
7573 -1 0
6450 -3 0
8023 -14 0
176 -13 0
8709 -8 0
7162 -4 0
1235 -8 0
120 -1 0
277 -7 0
4590 -4 0
7051 -6 0
7124 -19 0
7961 -13 0
1978 -20 0
1603 -23 0
8360 -13 0
145 -9 0
438 -16 0
8195 -13 0
6420 3 3
4161 -14 0
5022 -2 0
3963 7 7
3380 -16 0
1093 -14 0
6910 -11 0
9151 -1 0
2304 -13 0
8105 -3 0
4006 -19 0
355 -14 0
9128 -14 0
1789 0 0
2954 -7 0
3119 -7 0
6016 -20 0
2293 -22 0
5466 -2 0
7923 -5 0
8704 -24 0
3461 -1 0
514 -2 0
3915 -17 0
2704 -19 0
1509 -10 0
6654 -20 0
8603 -10 0
7580 -18 0
625 -12 0
7198 -19 0
4035 -18 0
8980 -10 0
469 -1 0
7294 -14 0
5387 -4 0
8148 -21 0
981 -13 0
8390 -15 0
4007 -9 0
4764 -7 0
3769 1 1
1030 -21 0
4303 -5 0
7404 3 3
4261 2 2
5666 -16 0
39 -20 0
856 -18 0
7585 -17 0
4326 -16 0
7919 -1 0
240 1 1
6553 -2 0
3550 -15 0
7691 -15 0
3908 -13 0
8529 7 7
2334 -18 0
2795 -15 0
8332 -5 0
5101 0 0
3738 1 1
1931 -15 0
5336 -14 0
3417 -14 0
8302 -15 0
1239 -12 0
8584 -16 0
6357 -5 0
1554 -15 0
3275 -19 0
5920 -28 0
941 -6 0
6298 -15 0
7411 -13 0
7608 -26 0
4841 -20 0
3898 -21 0
1127 -11 0
892 3 3
1213 -10 0
4014 -26 0
8111 -16 0
2944 -15 0
4889 -1 0
4574 2 2
711 -15 0
1148 -11 0
1761 -20 0
7910 0 0
6419 -6 0
8924 -22 0
1209 -21 0
4722 -1 0
2031 -18 0
5980 -4 0
8665 -12 0
5778 -17 0
7731 -12 0
6000 -13 0
5125 -2 0
8662 1 1
1723 -19 0
5464 -9 0
8277 -23 0
1078 0 0
4979 -7 0
6988 0 0
4517 -15 0
6214 -22 0
3935 -29 0
8336 -9 0
4393 -15 0
646 -12 0
8067 -6 0
1560 -4 0
2213 -16 0
1282 -7 0
5559 -10 0
5220 -6 0
5605 -2 0
6250 -2 0
6735 1 1
8024 -22 0
4585 -6 0
3350 -10 0
6403 0 0
2584 -7 0
4405 -14 0
7694 -4 0
7963 -11 0
1624 -24 0
57 -8 0
6842 -5 0
2843 3 3
9008 -19 0
4685 -4 0
3994 -16 0
2479 -22 0
60 -7 0
1725 -19 0
5110 -13 0
6731 -8 0
8732 -9 0
7677 -8 0
6386 -4 0
1007 -11 0
3788 -5 0
2253 3 3
3374 -7 0
2155 -21 0
2376 -11 0
4533 -20 0
3382 -2 0
739 1 1
5364 -19 0
7633 2 2
5002 -17 0
7503 -5 0
496 -14 0
7101 -12 0
7446 -1 0
5999 -20 0
716 -13 0
4849 -14 0
2482 1 1
4115 -21 0
3992 -10 0
2833 -17 0
1606 0 0
3243 -10 0
2412 -25 0
7333 -6 0
3070 -11 0
931 -13 0
8068 5 5
5969 -17 0
6026 0 0
523 -3 0
6908 -12 0
333 -19 0
3850 -3 0
451 -9 0
1308 -17 0
957 -11 0
3466 1 1
4215 -5 0
4636 0 0
8081 -1 0
8254 -15 0
563 0 0
9000 3 3
6101 -3 0
1006 -3 0
8551 5 5
7612 6 6
4133 -2 0
806 2 2
7263 5 5
6288 0 0
9 6 6
6166 4 4
3259 5 5
8752 2 2
4137 7 7
98 3 3
4171 10 10
3252 2 2
5405 0 0
4962 2 2
5411 1 1
3900 4 4
773 4 4
8518 10 10
8867 -6 0
6712 5 5
7509 2 2
8606 2 2
2463 -2 0
2892 1 1
4921 0 0
8082 1 1
3491 -5 0
4376 0 0
8241 3 3
7450 -5 0
5291 -3 0
6796 4 4
1813 3 3
7878 -3 0
2911 7 7
6144 5 5
7153 -2 0
7414 3 3
2491 -2 0
7108 -2 0
1621 1 1
5174 -1 0
4575 5 5
5120 4 4
4961 1 1
1002 1 1
87 4 4
1532
7169
4582
8467
4136
4861
358
4011
4056
677
7166
527
6200
6413
814
5847
104
3109
7786
851
3884
3605
2542
2667
3472
3153
9170
3527
7176
7929
7642
7995
5132
2837
898
151
8548
3557
1866
3083
3216
2605
5274
5219
1196
2437
7494
6675
300
5105
3030
8011
2300
8813
7098
3391
3732
2993
802
7971
208
3045
770
4463
6600
8989
3462
4923
7728
1945
2858
1767
6848
1781
4370
131
2916
673
7874
3619
64
1017
2018
3695
4956
6473
8538
5327
424
1501
8070
5615
4480
8177
434
8903
340
7197
8798
5167
1228
5945
1102
731
6780
4645
1902
5883
2904
2797
7978
3555
2220
4629
4734
9079
5876
8729
3622
4119
3284
8913
7742
8639
1208
313
7390
6739
7316
1493
314
2627
1380
6557
2842
4599
872
4753
1130
4947
3480
1737
2986
6627
8796
5521
2774
7463
7576
1333
1118
7071
3584
1673
5142
1067
2340
1733
3694
547
2660
125
3978
5311
3256
4697
1870
2375
124
8741
1498
4015
3348
6757
4130
5923
4996
8565
5134
2483
8524
7805
2074
2955
7528
657
1534
6815
3036
6949
6834
4259
6020
1585
2374
4887
4928
2257
2666
8235
8512
757
2178
2891
512
4269
4250
3027
3956
33
1918
3051
8880
8997
2046
7911
1656
3565
450
6071
6316
4225
2866
351
664
4881
5990
6895
7220
141
1146
2115
284
7937
6226
1811
8232
4457
7662
4455
8016
6001
362
3303
964
6813
3390
8495
8352
1561
6622
5095
4800
6569
718
4495
936
2597
6690
2231
7268
817
730
2371
832
2661
4534
7791
4552
4605
8982
115
5748
3869
1674
379
4880
1581
3082
7979
7964
6033
3870
6083
4192
4121
2930
7367
6224
2165
606
5975
4964
5057
2354
8427
2044
8265
3202
5083
6924
6673
654
6651
4548
5313
2902
3151
352
5073
8694
5231
8244
2333
7558
4519
5860
2389
6818
7091
44
4557
8306
531
5492
3321
6950
2071
8072
5261
2934
6659
3132
8301
6002
4771
7116
205
8162
7675
6740
2553
5306
8727
3976
5665
4802
6215
8344
2977
3634
1995
3180
8977
5586
5027
7664
4101
6470
4483
3060
2701
6626
4387
1708
3533
6114
3291
3088
6917
3326
1479
2792
4277
2834
6015
7056
1653
8682
7355
8348
2269
542
8131
8576
7773
7738
175
4300
3349
1994
7943
5164
6345
3678
3379
5856
3097
738
955
3820
5761
6154
5383
8856
4073
2254
7783
5968
1617
5882
7667
8156
8737
2206
7107
6384
1361
5334
3771
676
2757
5886
6959
5064
5701
7602
5903
8536
5681
1970
3019
5372
6217
7990
1431
2116
4693
2578
5467
3160
3345
2702
1863
8200
949
4882
1227
1536
6461
1490
5431
360
3657
2510
8331
692
9097
2690
3987
8304
885
7038
4839
4252
2673
2410
7687
4616
2449
5370
6571
364
2949
8590
5295
6088
7893
4842
8307
4940
133
2562
8299
6356
6317
8634
6679
8764
2013
9166
4235
1096
1793
2246
27
2980
7689
4262
903
3852
97
8934
8611
6164
3613
1126
5655
6572
4773
6762
7831
6644
1201
6138
8215
2880
7045
8186
3427
6532
2681
994
1503
7716
4069
2962
1703
9004
5393
7086
5515
3836
2929
2958
2411
2924
8429
1690
6719
1684
7781
6322
3163
7400
4969
8758
3075
4632
8789
2174
3599
3604
7305
8026
4231
1804
4885
5282
3803
4184
5537
9114
4267
4604
4573
5474
6507
2520
6561
2134
1507
7008
977
9098
7099
7800
7545
2234
2151
3084
7625
6418
4515
6568
5481
682
1695
1248
2405
3986
3895
6244
7797
3490
9139
4428
8209
3942
2511
3568
4505
8338
4039
8272
6549
4258
1611
6944
3133
3538
643
4012
8465
4490
6407
1876
1313
4658
4187
420
2209
1990
8651
8256
209
7454
4583
3704
7949
4102
1707
8296
2301
4050
1739
2632
5081
7278
8219
2172
3953
5150
8823
5328
4833
5314
7311
6964
8765
6422
3807
3476
2445
6078
491
3400
6725
622
3143
1768
2085
5594
3611
1480
7201
8090
2075
7364
2169
9070
2883
1928
1281
1150
1407
6364
4901
2450
607
1212
8777
4298
3703
3540
289
2466
6947
7188
5805
7538
8059
2976
8397
7354
3939
7628
1301
6782
4291
356
4365
5602
2195
4468
1129
538
8839
2496
1637
2342
1379
2148
8201
5358
8695
468
2885
1574
8907
8804
4105
5762
1391
24
8097
5422
6539
6028
169
5362
7987
6548
9021
6970
7703
1784
8425
2398
639
4720
2385
7526
1011
3080
8333
338
7551
1172
6685
7702
6547
4612
3157
8462
435
4140
4153
6438
975
280
8645
146
4847
2836
5661
5510
135
2208
6753
4838
9015
8032
3169
6338
331
4204
1997
2826
7431
4068
7725
7062
591
4044
2801
7270
463
8356
853
3530
2059
1024
5365
2870
8815
6744
1845
2214
5691
200
4461
6954
1363
4980
2225
8330
5455
5284
3449
2638
4019
2640
3665
1094
2147
3792
1545
282
8291
7772
3329
5534
5059
2000
6881
526
1791
2600
1573
4498
4991
4864
4413
6278
1779
4724
1677
4434
7147
8476
7129
2490
2699
6980
5933
3226
835
6716
6801
5514
8599
8288
3717
1062
7811
1012
7489
2974
8751
8900
5457
4930
1903
1120
6717
2038
155
7840
465
8002
8479
2012
4757
422
1855
444
7133
1782
8779
3944
2157
2022
2987
1864
3437
4762
4123
2576
5821
4706
4863
4
2465
2538
3375
6564
6189
3774
4731
5024
3609
1834
8747
436
8549
7966
5679
2324
4565
6890
3519
8592
4941
4542
6375
7540
7705
1430
6879
880
3337
4118
8047
5100
8501
7290
6095
2068
4017
2994
5211
2612
8621
4394
5055
2708
801
3562
5567
3804
8593
178
8355
5616
8889

Part 2 Questions 4-6

Enter your name in cell A2 of "Part 1 - Inflation" to generate data. Part 2 – Data Analysis: You have just completed a mission to Sierra Leone. The goal of the mission was to improve the quality of water in wells in a certain region. You collected data on the E. coli count from a sample of wells before and after your mission. You need to write a report on the success of the mission and for that you need to perform some statistical analysis on the data. You will be looking at the data from different perspectives to determine if the water quality has improved.
Before wells were dug E.Coli per ml After wells were dug E.Coli per ml Improvement Data: Before - After All computations use column D, the "Improvement" data.
25 20 5 min = -27 Color Key:
23 33 -10 max = 42 formula 4. Since you collected water from the same source twice it makes sense to analyze the amount by which each well’s water quality improved. Calculate a data set that would measure the change in the number of E. coli in each well, and the descriptive statistics for that data set, including both the standard deviation and standard error (SE) for the data set. (see section 3.5 of the textbook and the additional Excel document provided along with this major assignment). Make a frequency distribution and histogram for your data. (Calculate the improvement in the water quality of each well in column D. (Difference in Level of e. Coli.) Then, fill out the two tables to the left and make a histogram of the improvement levels. (NOTE: The Standard deviation of this data set is not the same as the standard error. Use the formulas from section 3.5 of the text to calculate the standard error of the means.))
25 11 14 mean= 9.7521367521 text/number
18 3 15 SD = 15.4521074174
23 1 22 count = 117
9 25 -16 SE = 1.4285478361
27 6 21
14 20 -6 Histogram - Width
27 15 12 Bin Width 6.2727272727
26 21 5
17 0 17 Frequency Distribution
1 11 -10 Low High Bins Cumulative Frequency Frequency 5. You have calculated one sample of wells and their improvement levels. If you could take all possible samples of wells of the, the distribution of all of those sample means would be a normal distribution. (see section 3.5). Find the 95% confidence interval of that distribution, using your sample mean as the population mean and the standard error of your sample as the population standard deviation. (Calculate the 95% confidence interval of the sampling distribution in cells F30 and H30.)
29 0 29 -27 -20.7272727273 [-27, -20.7) formula 1
13 24 -11 -20.7272727273 -14.4545454545 [-20.7, -14.5) formula 2
33 21 12 -14.4545454545 -8.1818181818 [-14.5, -8.2) formula 5
33 16 17 -8.1818181818 -1.9090909091 [-8.2, -1.9) formula 7
21 18 3 -1.9090909091 4.3636363636 [-1.9, 4.4) formula 15
36 25 11 4.3636363636 10.6363636364 [4.4, 10.6) formula 15 6. Recall that a 95% confidence interval shows a range of values that is 95% likely to contain the true value of a parameter. For example, if your 95% confidence interval for the average improvement level is (14, 18), it would mean that the true average improvement level is 95% likely to be in the range (14, 18). (See the additional Excel document provided along with this major assignment for more details.) Now answer each of these questions: a) Look at the mean for your improvement data, based on this what would you say about the change in the quality of water in the tested wells. b) Look at the confidence interval you calculated--is the value 0 inside or outside your confidence interval? c) If the value 0 is outside and less than your 95% confidence interval, can you conclude (with 95% confidence) that the water became cleaner? d) What if the value 0 were inside your confidence interval? Could you conclude (with 95% confidence) that the water became cleaner?
30 0 30 10.6363636364 16.9090909091 [10.6, 16.9) formula 13
2 0 2 16.9090909091 23.1818181818 [16.9, 23.2) formula 16
3 15 -12 23.1818181818 29.4545454545 [23.2, 29.5) formula 18
24 0 24 29.4545454545 35.7272727273 [29.5, 35.7) formula 12
13 21 -8 35.7272727273 42 [35.7, 42) formula 9
23 6 17 Remember to create the Histogram.
37 34 3
40 17 23
35 6 29 95% Confidence Interval
23 9 14 Lower number to Higher number
20 9 11 6.9664684718 to 12.5378050325
23 5 18
27 26 1
25 1 24 Answer Question 6 (a):
19 12 7 For the tested wells, the quality of water improved through the reduction of e. coli levels by an average of 9.51 units.
17 8 9
19 23 -4
28 14 14
25 23 2
32 31 1
28 0 28
16 31 -15 Answer Quastion 6 (b): Outside
33 0 33
6 28 -22 Answer Question 6 (c): No
26 7 19
3 14 -11 Answer Question 6 (d): Yes
27 0 27
6 33 -27
36 18 18
29 22 7
33 5 28
16 13 3
23 1 22
11 16 -5
33 0 33
19 5 14
19 20 -1
28 10 18
27 1 26
18 25 -7
36 12 24
29 0 29
9 21 -12
27 32 -5
31 30 1
29 31 -2
37 6 31
26 19 7
32 23 9
24 9 15
39 0 39
10 25 -15
35 0 35
1 12 -11
29 19 10
31 0 31
24 23 1
23 3 20
28 33 -5
39 23 16
35 5 30
17 37 -20
40 0 40
13 1 12
7 11 -4
22 3 19
8 31 -23
31 1 30
22 17 5
20 23 -3
30 28 2
33 13 20
19 21 -2
26 12 14
28 8 20
17 37 -20
34 7 27
17 0 17
17 25 -8
42 0 42
14 14 0
28 27 1
31 10 21
6 0 6
6 10 -4
31 23 8
32 7 25
21 0 21
22 21 1
26 12 14
30 21 9
33 19 14
25 30 -5
42 38 4
42 32 10
34 18 16
39 1 38
24 31 -7
30 0 30

Histogram

Amount of e. Coli in wells

-27 -20.727272727272727 -14.454545454545453 -8.1818181818181799 -1.9090909090909074 4.3636363636363651 10.636363636363637 16.90909090909091 23.181818181818183 29.454545454545457 35.727272727272727 1 2 5 7 15 15 13 16 18 12 9

Amount of e Coli

Frequency