Question

For this assignment, your group will utilize the preliminary data collected in the Topic 2 assignment. Considering the specific requirements of your scenario, complete the following steps using Excel. The accuracy of formulas and calculations will be assessed.

1. Select the appropriate discrete probability distribution. If using a binomial distribution, use the constant probability from the collected data and assume a fixed number of events of 20. If using a Poisson distribution, use the applicable mean from the collected data.
2. Identify the following: the probability of 0 events occurring, the probability of <5 events occurring, and the probability of ≥10 events occurring.
3. Using the mean and standard deviation for the continuous data, identify the applicable values of X for the following: Identify the value of X of 20% of the data, identify the value of X for the top 10% of the data, and 95% of the data lies between two values of X.
   
   How would I Calculate and do #3 and can it be explained step by step so I can better understand ?

Answer 1

STEP 1-Enter your data in the blocks or cells provided in your excel sheet. Take a random data of 20 size.

STEP 2-Now you have predefined functions in excel to perform and hence by using them you can take out the mean and variance.For that select the cells in which your data is entered and use equal to sign [=] before typing the function name . Following are some functions that can be useful for you :-

BINOMDIST(x, n, p, cum) where n = the number of trials, p = the probability of success for each trial and takes the value TRUE or FALSE.

BINOMDIST(x, n, p, FALSE) = probability density function f(x) value at x for the binomial distribution B(n, p), i.e. the probability that there are x successes in n trials where the probability of success on any trial is p.

BINOMDIST(x, n, p, TRUE) = cumulative probability distribution F(x) value at x for the binomial distribution B(n, p), i.e. the probability that there are at most x successes in n trials where the probability of success on any trial is p.

CRITBINOM(n, p, 1 – α) = critical value; i.e. the minimum value of x such that F(x) ≥ 1 – α, where F is the cumulative binomial distribution function for B(n, p)

Excel 2010/2013 provide the following additional functions: BINOM.DIST, which is equivalent to BINOMDIST, and BINOM.INV, which is equivalent to CRITBINOM.

Excel 2013 introduces the following new function (where x ≤ y ≤ n):

BINOM.DIST.RANGE(n, p, x, y) = the probability there are between x and y successes (inclusive) in n trials where the probability of success on any trial is p

Thus, BINOM.DIST.RANGE(n, p, x, y) = BINOMDIST(y, n, p, TRUE) – BINOMDIST(x–1, n, p, TRUE) if x > 0 and BINOM.DIST.RANGE(n, p, 0, y) = BINOMDIST(y, n, p, TRUE). The y parameter may be omitted, in which case BINOM.DIST.RANGE(n, p, x) = BINOMDIST(x, n, p, FALSE).

STEP 3: By looking at the data or the answer that you got identify your range of X and the percentage in which the value lies.That will be your final answer.