Microsoft Office Tutorials and References

In Depth Information

**Generating random numbers**

Generating random numbers using Bernoulli distribution

The Bernoulli Distribution option simulates the probability of success of a number of trials,

given that all trials have an equal probability of succeeding and that the success of one trial

has no impact on the success of subsequent trials. (Note that success in this context has no

value implication. In other words, you can use this distribution to simulate failure as readily

as success.) All values in the Bernoulli distribution’s output are either 0 or 1.

The probability that each cell will return a 1 is given by the distribution’s sole parameter—P

Value—for which you supply a number from 0 to 1. For example, if you want a sequence

of 100 random Bernoulli values whose most likely sum is 27, you define a 100-cell output

range and specify a P Value of 0.27.

Generating random numbers using binomial distribution

The Binomial Distribution option simulates the number of successes in a fixed number of

trials, given a specified probability rate. As with the Bernoulli Distribution option, the

trials are assumed to be independent; that is, the outcome of one has no effect on any other.

To generate binomially distributed numbers, you specify Number Of Trials and the P Value

(probability) that any trial will succeed. (Again, success in this context has no value

implication. In other words, you can use this distribution to simulate failure as readily as success.)

For example, suppose you make 10 sales presentations a week, you close the sale 20

percent of the time, and you would like to know what your success rate might be over the next

year. Type
50
(for 50 working weeks in the year) in the Number Of Random Numbers text

box,
0.2
in the P Value text box, and
10
in the Number Of Trials text box to learn that you

can expect to make no sales four weeks in the coming year.

Generating random numbers using Poisson distribution

The Poisson Distribution option simulates the number of times an event occurs within

a particular time span, given a certain probability of occurrence. The occurrences are

assumed to be independent; that is, each occurrence has no effect on the likelihood of

others.

The Poisson Distribution option takes a single parameter, Lambda, which represents the

expected outcome of an individual occurrence. For example, suppose you receive an

average of 10 service calls a day. You want to know how often you can expect to get 18 or more

service calls in a day over a year. To get this information, type
260
(52 weeks times 5 days)

in the Number Of Random Numbers box and
10
in the Lambda box (the expected

average). You can then use the COUNTIF function to count the number of times 18 shows up in

the output range. For more information, see “The SUMIF, SUMIFS, and COUNTIF functions”

in Chapter 14.