In this article, we will see how to generate some random names against time ranges in Excel, which will be used to schedule interview times for potential employees.
I have a list of interviewees for a job and to keep things fair I can use Excel to generate random times during the interview day for each candidate.
I already have a list of time slots reserved for one day. Each interview will last 45 minutes and will begin at 9 in the morning. I also have a list of names, whom I chose to interview based on their CVs.
I want to assign the names and times randomly. Therefore, we will use a series of formulas, which are really simple so that our interviewees have a random and equitable schedule. Now, we are dealing with a really small data set, but this method is also useful for larger data sets.
So here is an excerpt from my Excel spreadsheet with my already suggested time slots and my interviewees
Time slots
09:00
09:45
10:30
11:15
12:00
12:45
13:30
Interview candidates
Jason kane
Brennan hope
Jane smith
Kristen beatty
John smyth
Simon Westin
Randal Ives
I have seven time slots and seven candidates. So let’s get down to business and randomly assign these people to time slots.
We will use a series of formulas, RANDOM, LARGE and also SEARCHV. The method that I am going to show you will generate random numbers no duplicates.
Let’s start with RANDOM (). We will use this to generate a random real number between 0 and 1.
RANDOM () It doesn’t take any arguments in the formula, just type it in Excel and it will generate you a random number between 0 and 1. This will be our helper column in the process that I will keep in the columns to the left of my time slots. As we drag the formula down the column to the right of the time slots, we see that seven random numbers are generated. (If you keep pressing F9, Excel will continually recalculate the random numbers.)
Next, let’s use the BIG I work with another helper column that I put to the left of my list of names, to return the largest numbers from the first to the seventh in the dataset. (My random list of generated numbers is in column B of my Excel spreadsheet.
= BIG (B $ 4: B $ 10, ROW (A1))
This formula returns the k-th largest value in an array. We can specify the kth value using the ROW function that will also increase automatically as we go down the column. How cool is that?
As we drag the formula down the column, we select first, second, third, etc.
So the final piece of the puzzle is to use the fabulous VLOOKUP formula to look up the Values in our help column to the left of the names and return the time associated with it. Then just drag the formula down to fill in all the data.
= VLOOKUP (E4, $ B $ 4: $ C $ 10.2, FALSE)
Where E4 contains the largest random number and the range B4: C10 contains our random numbers and time slots.
That’s. Random times. It is not repeated.