One of the main elements of statistical analysis is making sure your data is a good representation of the population. The best way to do that is to make sure your sampled data is truly randomly selected.
I am going to show a quick method in Excel where we can select random names from a list (our population) of names. We will be using the following Excel functions to do so (if you are not familiar with them, don’t worry, I will walk you through it):
- index()
- randbetween()
We will also be creating a Named Range.
The List
Let’s start with the list. I have 14 names, and I want to randomly select 5 names from this list.
Index()
Index is a method for using the looking up elements in Excel using the indexes (row numbers, column numbers). Note the index is always a number, so when dealing with columns, don’t be tricked by the column letters assigned.
Here I am looking for the 5th index in my list of names
=index(list,index)
My result is Steve
You can also work horizontally. Here I am looking for index 3 from my horizontal list.
Result is d
Now watch this. I am asking for the 4th index in this list. What do you think it will be? The 4th row is Tyrone.
Nope, when running INDEX() the index starts at 1 – starting at the first element in the selected list. If you start counting with Gary, Karen is our 4th person down.
RANDBETWEEN()
RANDBETWEEN accepts 2 arguments, high and low numbers in a range. It then returns a random integer from with the range.
RANDBETWEEN(1,20) will return an integer from 1 to 20 (including 1 and 20)
Named Range
Now before me move forward, when formulas start to get larger (as they are about to) I look for ways to simplify the syntax. One trick would be to name our range.
Highlight all the names in your list, then go up to the name box and enter Names
Now combine RANDBETWEEN() and INDEX()
now try this: =INDEX(Names, RANDBETWEEN(1,14))
Hit enter and drag the formula down 5 cells
Now you have 5 random names selected from your list.