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
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.