Write frequency distribution (also known as a histogram) of the concentration of carcinogen in water (sample size = 20 water samples).
Write frequency distribution (also known as a histogram) of the concentration of carcinogen in water (sample size = 20 water samples).
Refer to the lab packet for formatting instructions
Directions: For questions 2-4, download the “Act I: A Mysterious Disease Workbook” from your Canvas assignment and refer to the sheet titled “Question 2,” containing the concentrations of carcinogen in water samples from the Allurian Forest (sample size = 20 water samples). Use Excel for calculations, modeling, and graphing. Round all calculated values to the nearest tenth of a decimal place. For example, if you calculate the value as 3.8218, round to 3.8. Use the Number formatting function in Excel on the Home Tab for all rounding in this course. Do not manually round numbers.
Use the sheet (tab) labeled “Question 2” to help me format and plot of a frequency distribution (also known as a histogram) of the concentration of carcinogens in water (sample size = 20 water samples). Your plot should follow the formatting guidelines listed below.
image.png
Hint: If you are having trouble with the cell formatting when entering the bin ranges in your spreadsheet, format only the bin range cells as “Text.”
Formatting Instructions
Chart type: 2D Column Chart
Chart title: “Frequency of Carcinogens in Water”; Font size = 20
Y-axis title: “Frequency”; Font size = 18
Y-axis numbers: Font size = 14
X-axis title: “Concentration of Carcinogen (µg/L)”; Font size = 18
X-axis numbers: Font size = 14
Bins = Use the following 6 bins: 0-1, 1-2, 2-3, 3-4, 4-5, 5-6
Based on the frequency distribution, does a normal probability distribution reasonably model the concentration of carcinogens in water?
Explain your answer to the previous question. Be sure to discuss the assumptions of a normal probability distribution and why, based on the frequency distribution, these assumptions seem appropriate (or not) for modeling the concentration of carcinogens in water.
Now that we have determined whether a normal probability distribution can reasonably model the data, we can calculate the probability of observing a certain concentration of carcinogen in the water of the Allurian Forest. Your approach to estimating this probability should depend on whether a normal probability distribution can reasonably model the data. Let’s compare two approaches: one that uses a normal probability distribution and one that uses only the data.
Note: You will answer both approaches (questions 5 and 6) regardless of your answers to questions 3 and 4.
Approach 1: Use the normal probability distribution to calculate the probability
Let’s assume that a normal probability distribution reasonably describes the data. Use the following approach to calculate the probability that the concentration of carcinogen in water exceeds the recommended limit of 10 µg/L:
(1) Calculate the mean and standard deviation of the carcinogen concentration.
(2) Use the normdist function of Excel to calculate the probability that a water sample has a carcinogen concentration greater than 10 µg/L, given the mean and standard deviation.
Directions: For question 5, use the Act I: A Mysterious Disease Workbook and refer to the sheet titled “Q5-6 Water Concentration Prob,” containing the concentrations of carcinogen in water samples from the Allurian Forest (sample size = 20 water samples). Use Excel for calculations, modeling, and graphing.
Assuming a normal probability distribution reasonably models the data, calculate the probability that water from Alluria contains more than 10 µg of carcinogen per liter (> 10 µg/L).
Excel tutorial:
#11 Calculating a Probability from a Normal Probability Distribution; #11 Calculating a Probability from a Normal Probability Distribution transcript)
The probability that the concentration of carcinogen in water exceeds the recommended limit of 10 µg/L =
ID Carcinogen concentration (µg/L)
1 1.91
2 2.82
3 5.29
4 0.08
5 0.96
6 0.87
7 5.10
8 4.59
9 4.72
10 1.89
11 2.28
12 0.13
13 2.80
14 1.95
15 3.91
16 1.50
17 3.04
18 0.32
19 0.08
20 3.62
Approach 2: Use the data to estimate the probability
Excel tutorials:
#6 Functions: Describing Data; #6 Functions: Describing Data transcript
#7 Functions: The Count Family and Abs Cell References; #7 Functions: Describing Data transcript
#8 Calculating Frequencies; #8 Calculating Frequencies transcript
Let’s assume that a normal probability distribution fails to describe the data reasonably. Use the following approach to estimate the probability that the concentration of carcinogen in water exceeds the recommended limit of 10 µg/L:
Count the number of water samples with more than 10 µg/L.
Divide the number of water samples with more than 10 µg/L by the total number of water samples (20). Convert this value to a percentage by multiplying by 100.
"Order a similar paper and get 100% plagiarism free, professional written paper now!"
