Sign in or start a free trial to avail of this feature.
10. Generating Random Numbers
Sometimes you may want to introduce randomness into your Excel spreadsheets. In this lesson, we’ll look at RAND and RANDBETWEEN, Excel’s two functions for generating random numbers, and use them to divide orders into multiple groups.
Using Random Numbers (00:17)
In this lesson, we want to select a sample of orders for review. We want to select the sample at random, and we can do this using two functions in Excel that generate random numbers.
Using The RAND Function (00:42)
RAND is a function that generates random numbers between 0 and 1. Generally, we use the output from this formula in another formula, like an IF statement. For example, say we want to review one in three orders. We could generate a random number for each order, then use an IF statement to select the order if this random number is less than 0.33.
There are a few things to be aware of when using RAND. Firstly, it generates different numbers every time you use it. Second, it recalculates every time the spreadsheet recalculates. As a result, you should use Paste Values to replace the RAND function with values once you have used the function correctly. This will hard code the results of the formula into the spreadsheet.
Using the RANDBETWEEN Function (03:15)
RANDBETWEEN generates random numbers between two specified limits. For example, RANDBETWEEN(1,3) generates either 1, 2 or 3, with each being equally likely. Here, we use RANDBETWEEN to divide orders into three categories: internal review, review with a survey, or no review. We use RANDBETWEEN to generate a category for each order, then use this in a nested IF statement to return the actual category.
In the previous lesson, we learned how to create a SUMPRODUCT formula with multiple conditions. In this lesson, we'll learn how to generate random numbers using the RAND and RANDBETWEEN functions. At the end of quarter one, we can see that the company has achieved 23 orders in total and these orders are for various products from different customers. We'd like to select a small sample of these orders to review as part of a quality control process and I'd like to pick these orders to review at random instead of following a fixed rule such as every third order. In Excel, we can do this by using a very helpful function called the RAND function, which is used for generating random numbers.
And the RAND function simply generates a random number between zero and one.
Generally, we take the output of the RAND function and use this output in another formula.
Here, we're going to use the RAND function to randomly select the orders we want to review.
Let's say we want to review approximately one in three of the orders for the quarter. To do this, we'll generate a random number for each order and if that number is below 0.33, we'll choose the order for review. I've created a new column called Review 1 to the right of our dataset. And I'll skip to the top and here, I'll write equals, IF to open an IF function.
And the logical test will be if a random number is less than 0.33. And if the answer is true, then I'll write Yes.
Otherwise, I'll write No.
And then I'll close the bracket and I'll copy down for the remaining cells.
As you can see, the RAND function has worked and selected a couple of random orders for us to include in our review.
There are a few gotchas with the RAND function, however. First, the RAND function generates different random numbers every time you use it, so you will get different values of Yes and No to meet.
Second, the RAND function is recalculated every time the spreadsheet recalculates.
So for example, if I enter a number in a cell and press Enter, you'll see that the RAND function has recalculated and some of our values have changed. I'll do this one more time to prove my point. The best way to deal with this is to replace the RAND formula with values once you've used the formula correctly. To do this, I'll simply select the values, copy with Ctrl + C, and then Alt + E + S + V to paste values.
And now, in my formula, I've hard-coded Yes and No.
I'll also delete these numbers under Group. Excel has another function for generating random numbers called RANDBETWEEN and this simply generates a random number that falls between an upper limit and a lower limit that you specify. In our example, let's say we want to change our review criteria. We want roughly one in three orders to be reviewed internally, and we want another third to be reviewed by sending a survey to a customer, and we want the final third to be not reviewed at all.
We'll do this by using the RANDBETWEEN function to fill out our Group column.
I'll move over to the right to give myself some space and write equals, RANDBETWEEN.
As I mentioned earlier, this takes two arguments, a bottom number and a top number. And I'll simply enter one and three as these arguments.
In this case, RANDBETWEEN will simply return a number that's either one, two, or three with each number being equally likely.
This does not mean that the actual number of rows in each group will be the same because there will be some random variation. So I may have more ones than I do twos.
To categorize each transaction randomly as either internal, survey, or none, I'm going to combine a nested IF function with RANDBETWEEN.
So I'll start by jumping back into the formula and writing equals, IF and I'll simply check if the random number generated is equal to one. And if it's equal to one, I'll return Internal.
Then I'll create another IF function that again checks if RANDBETWEEN, one and three, is equal to two. And if it is, I'll write Survey. And if it's not, I'll simply write None.
And I'll close my brackets and copy down for the remaining cells.
And as you can see, the random number generated in this example is the number three, so I get None for this row. And in this example, the random number generated is one, so I get Internal. To make sure these values don't change continuously, I'm going to copy and then Alt + E + S + V to paste values.
As we've seen, random numbers are a useful tool when dividing up your data in certain situations where you don't want to be affected by human biases and you want an element of randomness. There are downsides, however, particularly the frequent recalculation of the formula and the need to use Paste Special to convert your results into values.