Generate random numbers in MS Excel

A common requirement is to generate a set of random numbers that meet some underlying criterion. For example, a set of numbers that are uniformly distributed from 1 to 100. Alternatively, one might want random numbers from some other distribution such as a standard normal distribution.

While there are specialized algorithms to generate random numbers from specific distributions, a common approach relies on generating uniform random numbers and then using the inverse function of the desired distribution. For example, to generate a random number from a standard normal distribution, use =NORM.S.INV(RAND())

Another common requirement is the generation of integer random numbers from a uniform distribution. This might be to select people for something like, say, training, or a drug test. Or, it might be to pick a winner for a door prize at a social event. It might also be to assign players to groups for a sport tournament such as golf.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0806%20generate%20random%20numbers.shtml

Tushar Mehta

5 thoughts on “Generate random numbers in MS Excel

  1. [quote] It generates a uniform random number that is less than or equal to 0 and less than 1, i.e., the generated random number X satisfies the rule 0 <= X < 1.[/quote]

    I don't think so….

  2. RANDBETWEEN used to be part of the Analysis ToolPak (or ATP). So, before the ATP functions were integrated into Excel itself, one had to load the ATP add-in before using functions like RANDBETWEEN. Also, there are those who believe that the code in the ATP was not the greatest. [Is that diplomatic enough? {grin}] So, one has to decide between the simplicity of RANDBETWEEN and the uncertainty associated with the use of a ATP function.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.