Generating Random Numbers in Excel

Excel can be used to generate random numbers according to parameters that you set. To generate random numbers, use the RAND and RANDBETWEEN functions as follows:

Ensure that Random Functions are Turned On

1. Check to make sure that Excel will recognize the random function formulas by choosing Tools from the drop-down menu and clicking on Add-Ins.
2. In the Add-Ins Available list, make sure that Analysis ToolPak is checked, then click OK.

RANDBETWEEN

The RANDBETWEEN formula will generate a random integer between a specified set of numbers. The range for the random number is placed in parentheses, where the first number is the lowest allowed integer and the second is the highest allowed integer. For example, if I wanted to generate a random number between 1 and 100, the formula would look like this:

=RANDBETWEEN(1,100)

Simply copy and paste this formula into any worksheet cell and press Enter. You will see that a random number between 1 and 100 is displayed in that cell. To generate a new number, simply click in any empty cell and press Delete.

RAND

The RAND formula will generate a random decimal number greater than or equal to 0 and less than 1. The RAND formula is always followed by an empty set of parentheses. The formula looks like this:

=RAND()

Simply copy and paste this formula into any worksheet cell and press Enter. To generate a new number, simply click in any empty cell and press Delete.

To create a random decimal number greater than 1, simply multiply the RAND formula by the maximum allowed number you wish to use. For example, if I want to generate a random decimal number between 0 and 100, I would use the following formula:

=RAND()*100

If you wish to create random fractions, or limit the decimal places after zero to a specific number, simply change the format of the cell by following these steps:

1. With the cell you want to format selected, choose Format from the drop-down menu and click Cells.
2. Click on the Numbers tab and choose the display type for the number from the Category list. For fractions, select "Fraction". For decimals, select "Number".
3. If you selected "Fraction", choose the maximum size of the fraction allowed from the Type field and click OK.
4. If you selected "Number", choose the maximum number of decimal places allowed from the Decimal places field and click OK.

Spend some time playing with these formulas until you feel comfortable with them.

Return to Technology Tips