Using Excel to Build Randomizers

In Basic, Formulas by Garrett Dorman0 Comments

Share with:


Occasionally, the need for a random number will surface in Excel. Fortunately, creating a random number is quite simple, given Excel has a built in function for this task: the RAND function. Using this function, along with some other associated functions gives you all the tools you need to create any sort of randomizer you would need.

When creating a randomizer, the central function used is the RAND function. The RAND function looks like this:

=RAND()

It is a very simple function in principle, containing no arguments. However, there are some subtleties about this function that are very important. First, this function by itself will generate a random number between 0 and 1. It will generate a 0, but it will not generate a 1. An example of the RAND function can be seen below.

RAND Function

Second, it is important to note that this function will generate a new random number each time the spreadsheet is calculated. This means that changing a cell will cause a new random number to appear in any calculations this function is used in. There is, however, a workaround for this. Typing the RAND function into the function bar (not the cell) and pressing F9 will replace the formula with a single, unchanging random number. An example of this can be seen in the following image.

F9 Shortcut

While generating a random number between 0 and 1 can be somewhat useful in certain situations, using the RAND function in conjunction with some simple arithmetic and other functions really increases its usefulness and versatility. To demonstrate this, we will use an example case of the RAND function to generate a random number between 1 and 100. The first thing to do is to multiply the RAND function by 100, the number of possible random numbers. Multiplying by 100 would create a random number between 0 and 99.9999. . . We will also need to add 1 to the function because Excel begins the random number sequence at 0, and not at 1.

Lastly is removing the multiple decimal places to generate a random whole number. The INT function is the perfect tool for the job. The INT function looks like this:

=INT(Number)

This function will take only the whole number portion of a given Number argument. For example, using 5.51 as the Number argument would return 5, doing away with the .51. This is a perfect match to do away with the decimal points generated by the RAND function. Our completed formula, generating a random number between 1 and 100, looks like this:

=INT(RAND()*100+1)

It is also important to note that using the F9 key to lock a specific random number will work for entire formulas. If the F9 key is used to lock the above formula into place, the following is output:

INT Function

Using the RAND function, it becomes easy to create randomizers for lists of date. First, create the table you wish to randomize, giving each entry a random number. For this example, we will use the same formula as above to generate numbers between 1 and 100.

RAND List

Afterwards, there are two ways of randomizing the list of data. First is by using the Sort tool. Note, however, that with RAND being recalculated each time the spreadsheet is calculated, the numbers will not match the orders because a new random number will be generated when the data is sorted. However, this tool will still arrange the data in a random order.

Sort Tool

Another way to randomize the same data is with a simple IF statement and the MAX function. First, use the MAX function to find the maximum random value in the table using this formula, listing it with each entry:

=MAX($Table_Start:$Table_End)
Where Table_Start is the first entry of the table, and Table_End is the last entry in the table.

MAX Function

Then, using a simple IF statement, the table can be easily indexed to select a random option from the list. The following is the general IF statement and the output of the randomizer:

=IF(Random=Max,”>>>”,””)

IF Statement

As can be seen, randomizing data is very simple in Excel. Using the RAND function, along with the arithmetic learned, generating any random number is straightfoward. Combining this with the Sort tool, MAX function, and the IF function creates a versatile suite of skills to create any random data set needed.

Share with: