When the possible outcomes of an action all have the same probability, we say that the action has equally likely outcomes. When flipping a coin, heads or tails is equally likely (each has a probability of 1/2). When an ordinary die is rolled, the six possible outcomes are equally likely (each has a probability of 1/6).
To simulate an action with equally likely outcomes, the possible outcomes are associated with consecutive integer values. For example, the possible outcomes of a single die roll can very naturally be associated with the consecutive integers 1, 2, 3, 4, 5, and 6. The outcomes of a single flip of a coin can be associated with the consecutive integers 0 and 1 (which might represent the number of heads).
Excel provides a function that is perfect for simulating an event with equally likely outcomes. It is the RANDBETWEEN function. This function has two integer arguments (separated by a comma, as usual):
where low is less than high. The function returns a random integer value between low and high inclusive:
low ≤ RANDBETWEEN(low, high) ≤ high
Simulating the outcome of a random action with equally likely outcomes is a two step process. First, associate the possible outcomes for the action with a consecutive range of integer values. Second, use the RANDBETWEEN function to simulate the action with the first and last numbers in your range of integers as the two arguments to the function. Suppose we want to simulate the roll of a single die. The range of integer values associated with the outcomes of a die roll are 1 to 6. The expression, RANDBETWEEN(1, 6) simulates a die roll because the value returned by this function is a random integer in the range 1 to 6.
Often an experiment consists of two or more actions each of which has equally likely outcomes. For example, rolling a pair of dice or flipping a pair of coins. In situations like these, each individual action is simulated using an Excel expression. In some contexts (but not all) the results of the simulated actions can be added together.
For example, when rolling a pair of dice, you are not usually concerned with the specific outcomes per se, but rather the dot sum (the sum of the outcomes). For example, you may be looking for a dot sum of 4 and you don't really care whether the specific outcomes are 1 and 3, 2 and 2, or 3 and 1. Here is the Excel expression that can be used to generate the dot sum that results from rolling a pair of dice:
Dot Sum = Die 1 Result + Die 2 Result Dot Sum = RANDBETWEEN(1,6) + RANDBETWEEN(1,6)
For some actions, the outcomes are not equally likely. For example, when a baseball player goes to bat, the probability of getting a hit is less than the probability of not getting a hit. They are not equally likely outcomes. Or, suppose an Asbury student is selected at random. The probability that the selected student is female is higher than the probability that the student is a male. They are not equally likely outcomes.
Most programming languages include a function that generates a random decimal fraction in the range 0 up to but not including 1. In Excel, that function is the RAND function:
0 ≤ RAND() < 1
Like all Excel functions, the parentheses enclose the function arguments. In the case of the RAND function, however, the number of arguments is zero. That is, there are no arguments. Nevertheless, the parentheses are still required.
In some games, a special 6-sided die is used. The possible outcomes are 2, 3, 3, 4, 4, and 5. Notice that the outcomes are not equally likely:
The first step is to draw a line segment exactly one unit long and mark off distances along this line segment that correspond to the probabilities of the outcomes:
For each outcome, the distance corresponds to the probability of the outcome. Notice that, as usual, the sum of the probabilities (i.e., the distances) is one.
Next, determine the locations of the boundaries between adjacent outcomes. Each boundary is found by adding up the distances to the left of the boundary:
These boundary locations divide the line into segments whose lengths correspond to the probabilities of the corresponding outcomes. They are the key to writing an Excel expression to simulate the experiment.
Recall that, conveniently enough, the RAND function generates values in the range 0 up to but not including 1. That is, it produces values that correspond to points on the number line shown above. If the value generated by the RAND function is less than 1/6 then the outcome is 2. If the value generated by the RAND function is between 1/6 and 3/6 then the outcome is 3. If the value is between 3/6 and 5/6 then the outcome is 4. Finally, if the value generated by the RAND function is between 5/6 and 1 then the outcome is 4.
In the Excel expression below, X represents a cell reference to a cell on the worksheet that contains a random value generated by the RAND function.
=IF(X < 1/6, 2, IF(X < 3/6, 3, IF(X < 5/6, 4, 5)))
This expression is tied directly to the number line we drew earlier. In the illustration below, notice that the outcomes appear in the Excel expression in the same order that they appear on the line. Also note, that the boundary values appear in the less than comparisons in the same order that they appear on the number line.
Let's analyze the Excel expression a step at a time. In each sentence below, the comment refers to the text highlighted in a bold red font.
=IF(X < 1/6, 2, IF(X < 3/6, 3, IF(X < 5/6, 4, 5))) means if the random value is less than 1/6, then the simulated outcome is 2 and we are done.
Otherwise, =IF(X < 1/6, 2, IF(X < 3/6, 3, IF(X < 5/6, 4, 5))) means the random value is greater than or equal to 1/6 and if that random value is also less than 3/6 then the outcome is 3 and we are done.
Otherwise, =IF(X < 1/6, 2, IF(X < 3/6, 3, IF(X < 5/6, 4, 5))) means the random value is greater than or equal to 3/6 and if that random value is also less than 5/6 then the outcome is 4 and we are done.
Otherwise, =IF(X < 1/6, 2, IF(X < 3/6, 3, IF(X < 5/6, 4, 5))) means the random value is greater than or equal to 5/6 and since we know it must be less than 1, the outcome must be 5 and we are done.