Contextures

Excel RAND and RANDBETWEEN Functions

These examples show how to use the RAND and RANDBETWEEN function to create random numbers or random text in Excel

RAND Function

Use the RAND function to create random numbers in Excel. Then, use a column of random numbers for sorting or extracting records in a table.

To create a random number in a cell, use this formula:

=RAND()

Video: RANDBETWEEN Function

Use the RANDBETWEEN function to create random numbers in Excel. When combined with CHOOSE, it can even create random text. See the steps in this video, and the written instructions are below the video.

RANDBETWEEN for Random Numbers

If you give the RANDBETWEEN function a bottom number, and a top number, it will return a random number within that range.

For example, in the screen shot below, we are creating a set of scores for records in a sample data file.The lowest score should be 30, and the maximum is 100, so this is the formula in cell H2:

=RANDBETWEEN(30,100)

randbetween function 02

Copy the formula down, in column H, to fill in the remaining scores. go to top

RANDBETWEEN for Random Text - List

The RANDBETWEEN function can only return numbers, so by itself, it can't create a set of random text data. However, you can use RANDBETWEEN inside a CHOOSE formula. to return a random item from a list of values

In this example, we are trying to fill the Gender column, in a sample data file, that will be used for testing. For Gender, there are 2 values two values -- Male and Female -- so the RANDBETWEEN function uses 1 as the bottom value and 2 as the top.

For the Value arguments, each gender is entered, with double quote marks around each value.

=CHOOSE(RANDBETWEEN(1,2),"Male","Female")

randbetween function 04

Use Cell References as CHOOSE Values

In this example, the RANDBETWEEN function will be combined with CHOOSE, to return a random Region name in each row of a sample data workbook.

There are 3 regions, so the RANDBETWEEN function uses 1 as the bottom value and 3 as the top.

Instead of typing the region names, you can use absolute references to the cells which contained the region names. Cell references are a better choice if the values are long, or if they'll change frequently.

=CHOOSE(RANDBETWEEN(1,3),$K$4,$K$5,$K$6)

randbetween function 05

Paste As Values

The final step in setting up the random data is to copy the columns, and paste them as values. That will lock in the values, so they don't keep changing, as you work in the file.

  • Use the mouse to select the columns, then drag to the right, while pressing the RIGHT mouse button.
  • Drag the column back to its original location, and let go of the mouse button.
  • Then, in the popup menu, click on Copy here as Values Only

Download the Sample File

Related Links

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Functions

INDIRECT Function

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures

Search Contextures Sites

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

 

Excel Data Entry Popup List

 

 

 

Last updated: August 22, 2016 7:00 PM
Contextures RSS Feed