Using Excel (2007) to Compute Probabilities

Binomial Distribution

The BINOMDIST function in Excel computes both marginal and cumulative binomial probabilities.

=BINOMDIST(number_s, trials, probability_s, cumulative) Where: number_s = x (the number of success outcomes

trials = n (sample size)

probability_s = p (the probability of observing the success outcome on a single trial) cumulative = {true or false} (true = compute the cumulative probability up to and including x; false = compute only the marginal probability of x)

For Example 5.1, P(x = 2) is a marginal probability, hence set cumulative = false.

Thus to find P(x = 2), type =BINOMDIST(2,5,0.25,false) to give 0.264.

For Example 5.2(b), P (x ≤ 3) is a cumulative probability, hence set cumulative = true.

Thus to find P(x ≤ 3), type =BINOMDIST(3,10,0.20,true) to give 0.879.

For Example 5.2(c), to find P (x ≥ 2) = 1 − P(x ≤ 1) apply both the complementary rule of probability and the cumulative function (i.e. set cumulative = true).

Thus to find P(x ≥ 2), type =1-BINOMDIST(1,10,0.20,true) to give 0.624.

Poisson Distribution

The POISSON function computes both marginal and cumulative Poisson probabilities.

=POISSON(x, mean, cumulative)

Where: x = the number of occurrences of the observed event mean = a (the average number of occurrences)

cumulative = {true or false} (true = cumulative probability; false = marginal probability) For Example 5.3(a), P(x = 3) is a marginal probability, hence set cumulative = false. Thus to find P(x = 3), type =POISSON(3,5,false) to give 0.1404.

For Example 5.3(b), P(x ≤ 2) is a cumulative probability, hence set cumulative = true.

Thus to find P(x ≤ 2), type =POISSON(2,5,true) to give 0.12464.

For Example 5.3(c), P(x ≥ 5) = 1 − P(x ≤ 4). Apply both the complementary rule of probability and the cumulative function (i.e. set cumulative = true). Thus to find P(x ≥ 5), type =1-POISSON(4,5,true) to give 0.5595.

Standard Normal Distribution (z) – Finding Probabilities for z-values The NORMSDIST function computes cumulative normal probabilities for z-values.

=NORMSDIST(z)

Where: z = specified z-limit

This function computes the cumulative probability from (−∞ to the z-limit).

For Example 5.4(a), P(0 < z < 1.46) = P(z < 1.46) − P(z < 0) (where P(z < 0) = 0.5).

Thus to find P(0 < z < 1.46), type =NORMSDIST(1.46)–NORMSDIST(0) to give 0.4279.

For Example 5.4(b), P(−2.3 < z < 0) = P(z < 0) − P(z < −2.3).

Thus to find P(−2.3 < z < 0), type =NORMSDIST(0)–NORMSDIST(–2.3) to give 0.48928.

For Example 5.4(c), P(z > 1.82) is the complement of the cumulative probability from −∞

up to z = 1.82.

Thus to find P(z > 1.82), type =1–NORMSDIST(1.82) to give 0.0344.

For Example 5.4(d), P(−2.1 < z < 1.32) can be found by subtracting the two cumulative probabilities P(−∞ < z < 1.32) and P(−∞ < z < −2.1).

Thus to find P(−2.1 < z < 1.32), type =NORMSDIST(1.32)–NORMSDIST(–2.1) to give 0.8887.

Normal Distribution – Finding Probabilities for x-values

The NORMDIST function computes cumulative normal probabilities for x-values.

=NORMDIST(x, mean, standard_dev, cumulative)

The values x, mean (µ), and standard_dev (σ) are specified in the management problem.

Cumulative = true specifies that cumulative normal probabilities are computed.

This function computes normal probabilities directly for an x random variable with a given mean, µ, and standard deviation, σ, without first having to convert to a z-value.

For Example 5.5(a), P(45 < x < 51) can be found by subtracting the two cumulative probabilities P(−∞ < x < 51) and P(−∞ < x < 45).

Thus to find P(45 < x < 51), type =NORMDIST(51,45,8,true)–NORMDIST(45,45,8,true) to give 0.2734.

For Example 5.5(b), P(x < 48) is found by typing =NORMDIST(48,45,8,true) to give 0.64617.

Standard Normal Distribution – Finding z-limits

The NORMSINV (i.e. standard normal inverse) function finds the z-limit associated with the cumulative probability up to z.

=NORMSINV(cumulative probability)

For Example 5.6(a), to find k such that P(0 < z < k) = 0.3461, the cumulative area up to k must be used in the Excel function. The cumulative area is 0.5 + 0.3461 = 0.8461.

Thus type =NORMSINV(0.8461) to give 1.01985. Thus k = 1.02.

For Example 5.6(b), to find k such that P(z > k) = 0.8051, the cumulative area of the complement up to k must be used in the Excel function. The cumulative area up to k is 1.0 – 0.8051 = 0.1949.

Thus type =NORMSINV(0.1949) to give −0.85998. Thus k = −0.86.

Normal distribution – Finding x-limits

The NORMINV (i.e. normal inverse) function finds the x-limit associated with a normal probability distribution with a specified mean µ and standard deviation σ.

145

=NORMINV(cumulative probability, mean, standard_dev)

This function identifies the x-value directly (for a given mean, µ, and standard deviation, σ) without first having to identify a z-value and then convert it back to an x-value.

For Example 5.7(b), to find k such that the cumulative area up to k is 0.20, type

=NORMINV(0.20,244,68) to give 186.7698. Thus k = 186.8 (rounded).

5.10 Summary

This chapter covered three theoretical probability distributions. The binomial and the Poisson probability distributions are used to find probabilities for discrete numeric random variables, while the normal probability distribution calculates probabilities for continuous numeric random variables.

Each probability distribution’s specific properties are described and it was emphasised that these must be matched to a problem situation before the distribution can be validly applied to find probabilities.

The standard normal (z) probability distribution was introduced as a way of calculating probabilities for any normally distributed random variable x. The z-table was used to find the required normal probabilities. The standard normal (z) probability distribution was also used to find values of an x random variable associated with given probabilities.

Excel’s functions can also be used to compute binomial (BINOMDIST), Poisson (POISSON) and normal (NORMSDIST; NORMDIST) probabilities, while the inverse normal distribution functions (NORMSINV and NORMINV) can be used to find values of x for normally distributed random variables.

These probability distributions, and in particular the z-distribution, will be used extensively in inferential statistics when the topics of confidence intervals and hypothesis testing are discussed.

Exercises

1 Name two commonly used discrete probability distributions.

2 Indicate whether each of the following random variables is discrete or continuous:

(a) the mass of cans coming off a production line (b) the number of employees in a company

(c) the number of households in Gauteng that have solar heating panels (d) the distance travelled daily by a courier service truck.

3 (a) Use the binomial formula to find each of the following probabilities:

(i) n = 7, p = 0.2 and x = 3 (ii) n = 10, p = 0.2 and x = 4 (iii) n = 12, p = 0.3 and x ≤ 4 (iv) n = 10, p = 0.05 and x = 2 or 3 (v) n = 8, p = 0.25 and x ≥ 3

(b) Use the Excel function BINOMDIST to find the binomial probabilities in (a).

4 Once a week a merchandiser replenishes the stocks of a particular product brand in six stores for which she is responsible. Experience has shown that there is a one-in-five chance that a given store will run out of stock before the merchandiser’s weekly visit.

(a) Which probability distribution is appropriate in this problem? Why?

(b) What is the probability that, on a given weekly round, the merchandiser will find exactly one store out of stock?

(c) What is the probability that, at most, two stores will be out of stock?

(d) What is the probability that no stores will be out of stock?

(e) What is the mean number of stores out of stock each week?

Note: Calculate the probabilities in (b)–(d) using the binomial formula and then the Excel function BINOMDIST.

5 A telemarketing company that sells homeowner insurance has found that 15% of all calls made to households lead to a sale of a homeowner insurance policy. Assume that each call is independent of all other calls.

(a) Find the probability that no sales result from 12 calls.

(b) What is the likelihood that fewer than three homeowner policies are sold in 15 calls?

Note: Calculate the probabilities in (a) and (b) using the binomial formula and then the Excel function BINOMDIST.

6 A marketing manager makes the statement that the long-run probability that a customer would prefer the deluxe model to the standard model of a product is 30%.

(a) What is the probability that exactly three in a random sample of 10 customers will prefer the deluxe model?

(b) What is the probability that more than two in a random sample of 10 customers will prefer the standard model?

Note: Calculate the probabilities in (a) and (b) using the binomial formula and then the Excel function BINOMDIST.

7 A Tata truck dealer has established that 5% of new Tata trucks sold are returned for defective assembly repairs within their 12-month warranty period. Assume that the dealer has recently sold eight new Tata trucks.

147