Generate random numbers in MS Excel

This entry was posted in Data Analysis, Excel 14, Excel 15, Excel Intermediate, For Normal People, Worksheet Functions and tagged , , , , , , , , , on by .

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. snb

    [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 Post author

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax