How to Generate Random Numbers With Excel's RAND Function

01
of 01

Generate a Random Value Between 0 and 1 with the RAND Function

Generate Random Numbers with the RAND Function
Generate Random Numbers with the RAND Function. © Ted French

One way to generate random numbers in Excel is with the RAND function.

By itself, the function generates a limited range of random numbers, but by using RAND in formulas with other functions, the range of values, as shown in the image above, can easily be expanded so that:

  • By specifying the high and low values of a range, RAND can be made to return random numbers within a specified range - such as 1 and 10 or 1 and 100
  • The function's output can also be reduced to integers by combining the function with the TRUNC function, which truncates or removes all decimal places from a number.

Note: According to Excel's help file, the RAND function returns an evenly distributed number greater than or equal to 0 and less than 1.

What this means is that while it is normal to describe the range of values generated by the function as being from 0 to 1, in truth, it is more exact to say the range is between 0 and 0.99999999....

By the same token, the formula that returns a random number between 1 and 10 actually returns a value between 0 and 9.999999....

The RAND Function's Syntax

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the RAND function is:

= RAND ( )

Unlike the RANDBETWEEN function, which requires high and low end arguments to be specified, the RAND function accepts no arguments.

RAND Function Examples

Below are listed the steps required to reproduce the examples shown in the image above.

  1. The first enters the RAND function by itself;
  2. The second example creates a formula that generates a random number between 1 and 10 or 1 and 100;
  3. The third example generates a random integer between 1 and 10 using the TRUNC function;
  4. The last example uses the ROUND function to reduce the number of decimal places for random numbers.

Example 1: Entering the RAND Function

Since the RAND function takes no arguments, it can easily be entered into any worksheet cell simply by clicking on a cell and typing:

= RAND ( )

and pressing the Enter key on the keyboard. The result will be a random number between 0 and 1 in the cell.

Example 2: Generating Random Numbers between 1 and 10 or 1 and 100

The general form of the equation used to generate a random number within a specified range is:

=RAND() * (High - Low) + Low

where High and Low signify the upper and lower limits of the desired range of numbers.

To generate a random number between 1 and 10 enter the following formula into a worksheet cell:

=RAND() * (10 - 1) + 1

To generate a random number between 1 and 100 enter the following formula into a worksheet cell:

=RAND() * (100 - 1) + 1

Example 3: Generating Random Integers between 1 and 10

To return an integer - a whole number with no decimal portion - the general form of the equation is:

=TRUNC ( RAND() *(High - Low) + Low)

To generate a random integer between 1 and 10 enter the following formula into a worksheet cell:

=TRUNC (RAND() * (10 - 1) + 1)

RAND and ROUND: Reduce Decimals Places

Rather than remove all decimal places with the TRUNC function, the last example above uses the following ROUND function in conjunction with RAND to reduce the number of decimal places in the random number to two.

=ROUND(RAND()*(100-1)+2,2)

The RAND Function and Volatility

The RAND function is one of Excel's volatile functions. What this means is that:

  • The function recalculates - produces a new random number - every time the worksheet changes, and this includes actions such as adding new data.
  • Any formula that depends - either directly or indirectly - on a cell containing a volatile function will also recalculate every time a change in the worksheet occurs.
  • In worksheets or workbooks containing large amounts of data, volatile functions should be use with caution as they can slow down the program's response time due to the frequency of recalculations.

Start and Stop Random Number Generation with F9

Forcing the RAND function to produce new random numbers without making other changes to a worksheet can also be accomplished by pressing the F9 key on the keyboard. This forces the entire worksheet to recalculate - including any cells containing the RAND function.

The F9 key can also be used to prevent a random number from changing every time a change is made to the worksheet, using the following steps:

  1. Click on a worksheet cell, where the random number is to reside
  2. Type the function =RAND() into the formula bar above the worksheet
  3. Press the F9 key to change the RAND function into a static random number
  4. Press the Enter key on the keyboard to enter the random number into the selected cell
  5. Pressing F9 again will have no effect on the random number

The RAND Function Dialog Box

Almost all functions in Excel can be entered using a dialog box rather than entering them manually.  To do so for the RAND function use the following steps:

  1. Click on a cell in a worksheet where the function's results are to be displayed;
  2. Click on the Formulas tab of the ribbon;
  3. Choose Math & Trig from the ribbon to open the function drop down list;
  4. Click on RAND in the list;
  5. The function's dialog box contains information that the function takes no arguments;
  6. Click OK to close the dialog box and return to the worksheet;
  7. A random number between 0 and 1 should appear in the current cell;
  8. To generate another, press the F9 key on the keyboard;
  9. When you click on cell E1, the complete function = RAND ( ) appears in the formula bar above the worksheet.

The RAND Function in Microsoft Word and PowerPoint

The RAND function can also be used in other Microsoft Office programs, such as Word and PowerPoint, to add random paragraphs of data to a document or presentation. One possible use for this feature is as filler content in templates.

To use this feature, enter the function the same way in these other programs as in Excel:

  1. Click with the mouse in the location where the text is to be added;
  2. Type =RAND();
  3. Press the Enter key on the keyboard.

The number of paragraphs of random text varies depending on the version of the program used. For example, Word 2013 generates five paragraphs of text by default, while Word 2010 generates only three.

To control the amount of text produced, enter the number of desired paragraphs as an argument between the empty brackets.

For example,

=RAND(7)

will generate seven paragraphs of text in the chosen location.