Microsoft Excel 2007

Getting a Random Sample

Excel has the ability to generate random numbers from zero to one or from pre-selected number ranges. By using this feature, you can assign a random number to each row in a set of data and sort them randomly.

Getting a Random Sample
Generating a Random Number
Generating a Random Number within a Designated Range

Getting a Random Sample

When selecting a random sample from a collection of data you must first assign a random number to each row and then put the numbers into order.

If Column A is not already empty, add a column to the left of it
In Column A, select the rows of cells you would like to assign a random number to
EXAMPLE: If your data vertically spans 390 rows, select cells A2 through A390, but do not highlight the title cell A1.
In the Formula text box, type =RAND()
Press [Ctrl] + [Enter]
Column A now has random numbers corresponding to each row.
Excluding title cells, select all of your data
NOTE: This includes Column A.
From the Data tab, in the Sort and Filter group, click SORT
The Sort dialog box appears.
From the Sort By pull-down list, select Column A
From the Order pull down-list, select Smallest to Largest
Click OK
The Sort dialog box closes.
Your data is in order by the random numbers you assigned.
Select your sample by selecting the number of rows for the desired sample size
EXAMPLE: Select the first 200 rows.

Generating a Random Number

The =RAND() function creates a random number between 0 and 1 in the cell(s) you specify.

Select the cell(s) in which you want to create a random number
In the Formula text box, type =RAND()
If you have selected only one cell, press [Enter]
If you have selected multiple cells, press [Ctrl] + [Enter]
A random number appears in the desired cell(s).

Generating a Random Number within a Designated Range

The =RANDBETWEEN function creates a random number within the range you designate. In the cell(s) you have specified, a whole number will be generated, unlike the =RAND function which generates a decimal.

NOTE: If you are using the =RANDBETWEEN function for multiple cells and you do not want some of the generated numbers to be the same, be sure to designate a range larger than the number of cells you are applying the function to.

Select the cell(s) in which you want to create a random number
In the Formula text box, type =RANDBETWEEN([Bottom],[Top])
NOTE: Bottom is the number you want to be the lowest in your range, Top is the number you want to be the highest in your range.
EXAMPLE: To generate numbers between 1 and 50, in the Formula text box, type =RANDBETWEEN(1,50).
If you have only selected one cell, press [Enter]
If you have selected multiple cells, press [Ctrl] + [Enter]
A random number appears in the desired cell(s).