Excel Random Number Generation

| May 7, 2012 | 0 Comments

Whether you need to select a sample or need test data, Microsoft Excel is capable of generating random numbers which can satisfy these requirements and many more. Excel has two key functions which relate to the generation of random numbers: RAND() and RANDBETWEEN().

Random Number Generation (Duplicates Are Acceptable)

When randomly generating numbers, you must determine whether duplicate values are acceptable. In the case of generating sample data, duplicate numbers are natural and would be expected in the list of numbers. The following functions are perfect for generating such populations of numbers:

RAND()

The RAND() function generates a random number between 0 and 1. Since you may be interested in random number outside the range of 0 to 1, consider the following variations:

RAND()*(b-a)+a – This variation is used to generate a random number between two variables, “a” and “b.” By default, the generated number will have six numbers after the decimal.

Screenshot of the RAND() function usage

Screenshot from Microsoft Excel showing usage of RAND() function and result. Formula is as follows: =RAND()*(10-1)+1

TRUNC(RAND()*(b-a)+a,0) – This variation relies on the same formula described above and adds the TRUNC() function to ensure the randomly generated numbers are whole numbers. Change the “0” to the number of your choice to get a corresponding number of decimal places in your randomly generated number.

Example Excel Function TRUNC and RAND

Screenshot from Microsoft Excel showing the usage and result of formula: =TRUNC(RAND()*(10-1)+1

Alternatively, you can use the following function: INT(RAND()*100) to randomly generate a whole number between 1 and 100.

RANDBETWEEN()

The RANDBETWEEN() function generates a random number between the numbers specified in the function. It requires a minimum value and maximum value to be provided. For example, RANDBETWEEN(1,10) would result in a random number between 1 and 10, inclusive.

Example Excel Function RANDBETWEEN

Screenshot from Microsoft Excel showing the usage of the RANDBETWEEN() formula. The result is shown for the following formula: =RANDBETWEEN(1,10).

 

Random Unique Number Generation (Duplicates are NOT Acceptable)

Generating unique random numbers in Microsoft Excel is currently a tedious process for the average Excel user. Since there are multiple scenarios for generating random numbers, I’ve included the quickest two solutions below:

Randomization Tool

If you don’t really care if the numbers are generated in Excel, considering using the free random number generators available online. Consider the Random Sequence Generator (available from Random.org), which will generate a list of unique numbers between 1 and 1 billion!

Screenshot of Random.org Random Sequence Generator

Screenshot of Random.org Random Sequence Generator

The resulting sequence will have no repeating values and can easily be copied and pasted directly into Excel.

Helper Column Method

If you insist on creating the list of unique numbers in Excel, consider using a “helper” column of random numbers to sort a list of all numbers to be included in a population of random numbers. The steps are as follows:

  1. Create a list of all integers in the sequence in column A, starting with the first number in cell A1.
  2. In cell E1, add the following formula: =RAND()
  3. Copy the RAND() formula from E1 down the “E” column so that it mirrors the integers in column A.
  4. In cell C1, add the following formula: =IF(ROWS(C$1:C1)>B$1,””,INDEX(A:A,RANK(E1,OFFSET( E$1,,,COUNT(A:A)))))
  5. Copy the formula in cell C1 down the “C” column so that it mirror the integers in column A.
  6. Enter the number of unique values you want by changing the value in cell B1. For example, if you want 5 unique values from your integer list, enter the number 5.
  7. You should now have a list of unique numbers from your list. The list can be refreshed by pressing the F9 key on your keyboard
Excel Helper Column Method Screenshot

Screenshot from Microsoft Excel to show usage of "Helper Column Method" described above.

The Helper Column method is not visually appealing or intuitive, but it is a “quick and dirty” way to get random numbers out of Excel. Let us know if you’ve found alternative ways to generate random numbers in the comments section below!

Related Posts:

Tags: ,

Category: Software

Comments (0)

Trackback URL | Comments RSS Feed

There are no comments yet. Why not be the first to speak your mind.

Leave a Reply