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. Then you will copy and paste the random values to another column, which you will then sort in ascending order.

If Column A is not already empty, add two columns 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]
NOTES:
Column A now displays random numbers corresponding to each row.
Each cell, however, contains a formula and cannot be sorted.
Select all of the cells containing the random numbers that have just been generated
On the Home tab, click COPY
Place your insertion point in the first cell at the top of Column B
On the Home tab, from the Paste pull-down menu, select Paste Values
NOTES:
Column B now contains random numerical values that can be sorted.
The numbers will not match the values in Column A.
Delete Column A
HINT: This column contains the formulas, which are no longer needed.
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 menu, select Column A
From the Order pull down-menu, select Smallest to Largest
Click OK
The Sort dialog box closes.
Your data is in order by the random numbers.
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]

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 selected only one cell, press [Enter]
If you have selected multiple cells, press [Ctrl] + [Enter]