I need to do this problem in Excel. I need the answers to be in formulas. 4. Con

Question

I need to do this problem in Excel. I need the answers to be in formulas.

4. Consider a process in which bottles are filled with soft drink. The label on each bottle advertises 1.9 liters of soft drink, and any bottle that contains between 1.8 and 2.10 liters is considered acceptable. Put another way, the specification limits for the process are 1.95±0.15 liters. The data shown in the “Q.4” worksheet of the attached spreadsheet represents the amount of soda contained in a random sample of 200 bottles.

(a) Determine the percentage of bottles in the sample that conform to specifications.

(b) Estimate the probability that a randomly selected bottle conforms to specifications assuming that the data can be described as a normal distribution.

(c) Repeat part (b) assuming the data is exponentially distributed.

(d) Repeat part (b) assuming the data is uniformly distributed.

(e) Rank the three distributions (normal, uniform, and exponential) from best to worst in terms of which most likely represents the population data from which this sample data was taken. Explain your ranking.

Explanation / Answer

a) The percentage of bottles in the sample that conform to specifications
= 181 / 2
= 90.5%.
[ Enter the data .
In the cell beside, type the formula "=IF(AND(A1<2.1,A1>1.8),1,0)", where you substitute A1 by the cell number containing the value.
Drag to copy the formula over all the cells.
Here, we put 1 if it conforms to the specifications and 0 if it doesn't.
The average of these cells multiplied by 100 gives the required percentage. ]

b) The probability that a randomly selected bottle conforms to specifications assuming that the data can be described as a normal distribution
= P[ 1.8 < X < 2.1] , X~ Normal (1.95, (0.15/3)2)
= P [ X<2.1] - P[X<1.8]
= 0.9973002

c) The probability that a randomly selected bottle conforms to specifications assuming that the data can be described as a exponential distribution
= = P[ 1.8 < X < 2.1] , X~ Exponential (1.95)
= P [ X<2.1] - P[X<1.8]
= 0.05665267

d) The probability that a randomly selected bottle conforms to specifications assuming that the data can be described as a uniform distribution
= P[ 1.8 < X < 2.1] , X~ Uniform(1.8,2.1)
= P [ X<2.1] - P[X<1.8]
= 1

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.