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 Comments

  1. snb says:

    [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. Tushar Mehta says:

    Thanks for the heads up. :)

  3. LK says:

    What is your recomendation regarding Randbetween()?

  4. Tushar Mehta says:

    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.

  5. ross says:

    its 0 >= X <=1….?

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: