Random Assignment Vs Random Sampling In Excel

On By In 1

Many statistics and research books contain random number tables
similar to the sample shown below.

How to use a random number table.

  1. Let’s assume that we have a population of 185 students and each student has been assigned a number from 1 to 185. Suppose we wish to sample 5 students (although we would normally sample more, we will use 5 for this example).
  2. Since we have a population of 185 and 185 is a three digit number, we need to use the first three digits of the numbers listed on the chart.
  3. We close our eyes and randomly point to a spot on the chart. For this example, we will assume that we selected 20631 in the first column.
  4. We interpret that number as 206 (first three digits). Since we don’t have a member of our population with that number, we go down to the next number 899 (89990). Once again we don’t have someone with that number, so we continue at the top of the next column. As we work down the column, we find that the first number to match our population is 100 (actually 10005 on the chart). Student number 100 would be in our sample. Continuing down the chart, we see that the other four subjects in our sample would be students 049, 082, 153, and 164.
  5. Researchers use different techniques with these tables. Some researchers read across the table using given sets (in our examples three digit sets). For our class, we will use the technique I have described.

Microsoft Excel has a function to produce random numbers.

The function is simply

=RAND()

Type that into a cell and it will produce a random number in that cell. Copy the formula throughout a selection of cells and it will produce random numbers between 0 and 1.

If you would like to modify the formula, you can obtain whatever range you wish. For example.. if you wanted random numbers from 1 to 250, you could enter the following formula:

=INT(250*RAND())+1

The INT eliminates the digits after the decimal, the 250* creates the range to be covered, and the +1 sets the lowest number in the range.

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()

Random Assignment

To randomly assign numbers or tasks to a group of people, use the RAND function. In this example, there are 5 people, and the numbers 1 to 25.

  • Starting in cell A1, type the list of people's names.
  • Select all the names, and point to the fill handle, in the bottom right corner.

  • When the pointer changes to a black plus sign, drag down to row 25. This will repeat the name list down the column.

  • Leave column B blank
  • Starting in cell C1, create the list of numbers or tasks to be randomly assigned.
  • NOTE: For a sequential list of numbers, type the first two numbers, then select those tw0 numbers, and drag down to row 25.

  • In cell D1, type a RAND formula, to create a random number
  • =RAND()

  • Fill the RAND formula down to row 25
  • Then, right-click on any number in D1:D25, and in the popup menu click Sort, then click Sort Smallest to Largest

  • This sorts the list of 1 to 25 in random order
  • Finally, delete column D (the RAND formulas), and delete column B (the blank column)
  • Each name has a randomly assigned number, from the list of 1 to 25

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)

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

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")

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)

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.

Last updated: February 27, 2018 4:30 PM
Contextures RSS Feed

0 comments

Leave a Reply

Your email address will not be published. Required fields are marked *