How to Use RAND and RANDBETWEEN Functions in Excel

There are times when we wish to simulate randomness without actually performing a random process. For example, suppose we wanted to analyze a specific instance of 1,000,000 tosses of a fair coin. We could toss the coin one million times and record the results, but this would take awhile. One alternative is to use the random number functions in Microsoft’s Excel. The functions RAND and RANDBETWEEN both provide ways to simulate random behavior. The RAND Function We will begin by considering the RAND function. This function is utilized by typing the following into a cell in Excel: RAND() The function takes no arguments in the parentheses. It returns a random real number between 0 and 1. Here this interval of real numbers is considered a uniform sample space, so any number from 0 to 1 is equally likely to be returned when using this function. The RAND function can be used to simulate a random process. For example, if we wished to use this to simulate the tossing of a coin, we would only need to use the IF function. When our random number is less than 0.5, then we could have the function return H for heads. When the number is greater than or equal to 0.5, then we could have the function return T for tails. The RANDBETWEEN Function A second Excel function that deals with randomness is called RANDBETWEEN. This function is utilized by typing the following into an empty cell in Excel. RANDBETWEEN([lower bound], [upper bound]) Here the bracketed text is to be replaced by two different numbers. The function will return an integer that has been randomly chosen between the two arguments of the function. Again, a uniform sample space is assumed, meaning that each integer is equally likely to be chosen. For example, evaluating RANDBETWEEN(1,3) five times could result in 2, 1, 3, 3, 3. This example reveals an important use of the word â€Å"between† in Excel. This is to be interpreted in an inclusive sense to include the upper and lower bounds as well (as long as they are integers). Again, with the use of the IF function we could very easy simulate the tossing of any number of coins. All we would need to do is use the function RANDBETWEEN(1, 2) down a column of cells. In another column, we could use an IF function that returns H if a 1 has been returned from our RANDBETWEEN function, and a T otherwise. Of course, there are other possibilities of ways to use the RANDBETWEEN function. It would be a straightforward application to simulate the rolling of a die. Here we would need RANDBETWEEN(1, 6). Each number from 1 to 6 inclusive represents one of the six sides of a die. Recalculation Cautions These functions dealing with randomness will return a different value upon each recalculation. This means that every time that a function is evaluated in a different cell, the random numbers will be replaced by updated random numbers. For this reason, if a particular set of random numbers is to be studied later, it would be worthwhile to copy these values, and then paste these values into another part of the worksheet. Truly Random We must be careful when using these functions because they are black boxes. We do not know the process Excel is using to generate its random numbers. For this reason, it is difficult to know for certain that we are obtaining random numbers.