Excel assignment, due 10/31 9am
JJJQQQZZZ
Assignment4.docxDue Date and Time: October 31st , Before 9 AM.
All assignments must be typed in MS Excel (No MS Word No PDF) and must be submitted via Canvas.
1. Explain Pareto Analysis (80/20 rule) briefly and give a short example. (20 pts)
2. The accompanying data set consists of observations on shower-flow rate (L/min) for a sample of n=130 houses in Perth, Australia (“An Application of Bayes Methodology to the Analysis of Diary Records in a Water Use Study,” J. Amer. Stat. Assoc., 1987: 705–711) (read down, then across, build one column and sort the data from lowest to highest)
|
4.6 |
12.3 |
7.1 |
7.0 |
4.0 |
9.2 |
6.7 |
6.9 |
11.5 |
5.1 |
|
11.2 |
10.5 |
14.3 |
8.0 |
8.8 |
6.4 |
5.1 |
5.6 |
9.6 |
7.5 |
|
7.5 |
6.2 |
5.8 |
2.3 |
3.4 |
10.4 |
9.8 |
6.6 |
3.7 |
6.4 |
|
8.3 |
6.5 |
7.6 |
9.3 |
9.2 |
7.3 |
5.0 |
6.3 |
13.8 |
6.2 |
|
5.4 |
4.8 |
7.5 |
6.0 |
6.9 |
10.8 |
7.5 |
6.6 |
5.0 |
3.3 |
|
7.6 |
3.9 |
11.9 |
2.2 |
15.0 |
7.2 |
6.1 |
15.3 |
18.9 |
7.2 |
|
5.4 |
5.5 |
4.3 |
9.0 |
12.7 |
11.3 |
7.4 |
5.0 |
3.5 |
8.2 |
|
8.4 |
7.3 |
10.3 |
11.9 |
6.0 |
5.6 |
9.5 |
9.3 |
10.4 |
9.7 |
|
5.1 |
6.7 |
10.2 |
6.2 |
8.4 |
7.0 |
4.8 |
5.6 |
10.5 |
14.6 |
|
10.8 |
15.5 |
7.5 |
6.4 |
3.4 |
5.5 |
6.6 |
5.9 |
15.0 |
9.6 |
|
7.8 |
7.0 |
6.9 |
4.1 |
3.6 |
11.9 |
3.7 |
5.7 |
6.8 |
11.3 |
|
9.3 |
9.6 |
10.4 |
9.3 |
6.9 |
9.8 |
9.1 |
10.6 |
4.5 |
6.2 |
|
8.3 |
3.2 |
4.9 |
5.0 |
6.0 |
8.2 |
6.3 |
3.8 |
6.0 |
8.3 |
a. Calculate the sample median, mode, and mean of the data. (15 pts)
b. Calculate the sample variance and sample standard deviation. (10 pts)
c. Calculate the minimum and maximum values in the data set (10 pts)
d. Calculate the sample range and quartiles of these data. (10 pts)
e. Construct a box plot of the data set. Use the website provided in the Week 9 Lecture slides, copy and paste the image on your Excel file (15 pts)
f. Calculate the skewness and kurtosis of the data set and evaluate the findings (10 pts)
g. Construct a histogram for these data (10 pts)
Note: You can use either Excel Data Analysis Toolpack or calculate them manually with using the formulas.
Box Plot (Box and Whisker)
•No easy way to create Box Plots in Excel
•Reference video for how to do it in Excel:
https://www.youtube.com/watch?v=bgaN446TQXo
There are also Excel Add-ins you can find (free & paid)
–XL-Minor Add-in
https://www.meta-chart.com/box-and-whisker#/display
