mat-144 Mathematics

Description

Please review and complete the documents. for name put Justin

Unformatted Attachment Preview

Guidance for Topic 5 DQ 2
1. As always, first enter your name. Then, the data show up.
2. To find the min, use =MIN(B2:B103)-0.1. For the max, use =MAX(B2:B103)+0.1. These
adjustments by 0.1 assure that the minimum and the maximum values are included in the
frequency table. For the bin width, use =(E6-E5)/11. We divide E6-E5 by 11 which is the
number of the intervals in the frequency distribution table.
3. In D11, enter =E5. In E11, enter =D11+$E$7. Note E& is the bin width.
4. Next, enter =E11 in D12 and drag down the formula. (You just copy the high end of the
previous bin to the loa end of the next.)
5. Drag down E11 to E12. Then drag (or cut-paste) D12:E12 on down.
6. The title of bin has to be the midpoint of the lower and upper limits. Enter the formula in
F11 as shown, and drag down the formula.
7. Now, you need to fill out the frequency column. You may use =frequency(). But, I show
how to use =countifs() to fill out the column. These sites explain how to use =countifs().
https://exceljet.net/excel-functions/excel-countifs-function
https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bcaa8c2a866842
Here is a video made specifically for this class. (Try this one first and use the above if you
want more.)
8. The relative frequency is =frequency / 102. There are 102 wells.
9. For the mean change in F29, you need to use =average() for B2:B103.
10. How to create a histogram is very similar to how to create a pie chart which is explained
in topic 4 DQ 1.
This is also discussed in the video provided above, here is the link again.
Excel Skills Learned
Upon completing the sheet, you should have an understanding of:
1. Use of the =COUNTIFS() (or =FREQUENCY()) functions as functions
that operate on a range of values.
2. Adding and formatting a histogram correctly where you have set
up the bins exactly.
Math Skills Learned
1. How to create a frequency and relative frequency table.
2. How to visualize a frequency distribution via a histogram.
3. Start thinking about how to draw conclusions from data.
Instructions: Open the tab la
the tasks indicated there. Sta
indicated. Pay attention to th
self-grading.
Instructions: Open the tab labeled “Water Quality” and complete
the tasks indicated there. Start by entering your name where
indicated. Pay attention to the legend. This sheet is not currently
self-grading.
Well
Change in
water quality
Input name below to
generate the data
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Your Name Here
Video for this
Bin Table
Minimum
Maximum
Bin Width
Lower Limit
Frequency Distribution
Upper Limit
Title of bin
2. Based on the frequency distribution above, using the Title of Bins and Frequencies,
the mean Difference in water quality using an Excel formula with cell references. Form
mean Difference in water quality as a Number with 2 decimal places.
Mean change in water quality
3. Based on your Frequency Distribution from Part 2, create a histogram and place it b
each bin using the Title of Bin from the Frequency Distribution table. Please include ax
an appropriate chart title.
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
90
91
92
93
94
95
96
97
98
99
100
101
102
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Enter Name
Video for this
Legend
If a cell is shaded
Blue
Green
Gold
Any other color
You should
Enter a text response
Enter a number
Enter an Excel formula
Make no changes
Distribution
Frequency
Relative Frequency
ng the Title of Bins and Frequencies, calculate
cel formula with cell references. Format the
th 2 decimal places.
rt 2, create a histogram and place it below. Title
y Distribution table. Please include axis labels and
1. Create a frequency distribution of the water quality differences for the 102 well
First, Insert your name into cell D2 and hitting enter. Then find the Minimum, Max
using Excel formulas with cell references and Excel built-in functions where approp
Maximum, and Bin Width as Numbers with 1 decimal places.
Next, find the lower limit and upper limit of each class using the Bin Table values b
for each of those classes. Also, find the relative frequency for each class. The title o
midpoint of the lower and upper limits. Excel formulas with cell references should
Format all lower and upper limits, and bin titles as Numbers with 1 decimal places.
with 0 decimal places and format relative frequencies as Percentages with 0 decim
ater quality differences for the 102 wells.
ing enter. Then find the Minimum, Maximum, and Bin Width of the data
nd Excel built-in functions where appropriate. Format the Minimum,
1 decimal places.
f each class using the Bin Table values below, and then find the frequency
ative frequency for each class. The title of the bin should reflect the
cel formulas with cell references should be used for all calculations.
titles as Numbers with 1 decimal places. Format all frequencies as Numbers
requencies as Percentages with 0 decimal places.
Guidance for Topic 5 DQ 1
1. As always, first enter your name. Then, the data appear.
2. Enter the formula as shown and drag down the formula to fill out column F.
3. Excel formulas:
Mean: =average()
Median: =median()
Standard deviation: =stdev()
Range: =max() – min().
4. Next you need to find percentile. The explanation of the percentile function is here:
https://support.microsoft.com/en-gb/office/percentile-function-91b43a53-543c-470893de-d626debdddca
5. The explanation about the normal distribution can be found here:
https://www.mathsisfun.com/data/standard-normal-distribution.html
Now, you are asked to find the =mean + standard deviation and =mean – standard deviation.
Between these limits, there are 68% of the data.
Excel Skills Learned
Upon completing the sheet, you should have an understanding of:
1. Use of the =MIN(), =MAX(), =MEDIAN(), =AVERAGE(), =STDEV.P()
range functions.
2. Additional practice wth autofill.
Math Skills Learned
1. Basic calculations of statistics, median, average (mean), stanfdard
deviation, and range.
2. Practice with the “Empirical Rule”
3. Start thinking about how to draw conclusions from data.
Instructions: Open the tab la
the tasks indicated there. Sta
indicated. Pay attention to th
self-grading.
Instructions: Open the tab labeled “Improvement” and complete
the tasks indicated there. Start by entering your name where
indicated. Pay attention to the legend. This sheet is not currently
self-grading.
1. Enter your full name in the cell to the
right. If your full name is less than 5
letters long, add additional letters ‘X’ at
the end until you reach length 5.
Your Name Here
Well
Before Treatment
After Treatment
1
2
3
4
5
6
7
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Assignment Advisory: You must
GCU; contact the Help Desk for more inform
program may result in missing or corrupted
Improvement Data:
Before – After
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
Enter your name
ment Advisory: You must use the latest desktop version of Excel for Microsoft 365 for this assigment. (This is provided free by
tact the Help Desk for more information and help installing the software.) Using an earlier version of Excel or a different spreadsheet
may result in missing or corrupted template elements. Copying cells from or into this template may likewise result in corrupted data.
2. For the water test data to the left, start by using Excel formulas with cell references
to find the difference in test scores (Before – After) for each well. Next, use Excel built-in
functions to calculate the mean, median, standard deviation, and range for the before,
after, and improvement data. Format each value as a Number with zero decimal places.
Before
After
Improvement
Mean
Median
Standard Deviation
Range
3. For the improvement data in column F, use Excel built-in functions to calculate the
following percentiles. Format each value as a Number with 2 decimal places.
Percentiles
22nd
74th
4. Use the calculations above and the Empirical Rule to calculate the upper and lower
limits which 68% (~68.27%) of the differences in scores fall between. Use Excel formulas
with cell references and format each value as a Number with two decimal places.
68% of Differences
Lower Limit
Upper Limit
assigment. (This is provided free by
of Excel or a different spreadsheet
y likewise result in corrupted data.

Purchase answer to see full
attachment