A small company has decided to start doing random drug testing. They want to randomly select two employees each month. In episode 1491, Bill builds a handy workbook. Enter the employee names in B2:B101, then press F9 to select two employees, all with formulas, no VBA. Some people asked about downloading this workbook. It is here: Podcast1491RandomDrugTest.xlsx
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1491: Random Drug Testing.
This is a cool question sent in.
Small company of 26 employees, 26 employees, and every month, they want to randomly select 2 of those employees to be drug tested and they want to use Excel to do that, and they said, well, right now, we have 26 but we might have more or less employees in the future so we want to be able to have this grow.
So, let's see.
Took the whole spreadsheet and changed it to be one certain color.
Took the range where they're going to put the employee names and did no fill there so that way they know where they're allowed to enter those values.
Then, to the left, over here in column A…and I made it a real faint grey so that way no one would be changing it, and put a nice little formula that says…well, here.
Let's look at it up here.
We're using just =RAND().
That gives us a random decimal between 0 and 1, but then I said, hey, if B2 is blank, if that employee, if there's no employee to the right of me, then use a really large number.
In this case, 2 is a huge number because it's larger than RAND will ever give us.
We want to make sure those blank cells never show up in the next part, alright?
So, every time we press F9, out there on the left hand side, we're getting a new set of random numbers.
Down here where there's no employees, where there might be future employees, we're getting just the number 2 all the time, alright.
[ =IF(ISBLANK(B2),2,RAND()) ] Now, here's what I'm doing.
I put the 3 numbers, 1, 2, 3.
I know they asked for 2 -- I wanted to show you how it works for 3 though -- and then I ask for the small of A $ sign 2, A $ sign 101, that whole list of employees , D4.
So, the small of that range , 1 gives me the smallest value.
That's the same as MIN.
No arguments there, but small is really good because now it will give me the second smallest value and the third smallest value and so on.
Well, in this case, I’m only going for 3.
[ =SMALL(A$2,A$101,D6) ] Now that I know these numbers, I'm going to use VLOOKUP to go figure out which employee is associated with that number, and it might just be me being superstitious but I made sure that all this flows left to right.
That way, Excel calculates the random numbers over in A first, then it calculates the small here, and before it has a chance to go back and recalculate the random numbers in A, it does the VLOOKUP.
So, I’m going left to right.
So, VLOOKUP of that small number over here in A2 to B102, I want the second column, and I of course want an exact match.
So, every time I press F9, we're getting a new set of employees.
[ =VLOOKUP(E4,$A$2:$B$101,2,FALSE) ].
When you open the file, it's going to recalculate.
So, of course, just as soon as you open the file, you're going to have some random set.
I suggested…I realized this is…every single one is random.
Just come up with a policy that says we're going to open the file, we're going to press F9 6 times, and whoever's up there then at that point are the people.
So, open it, 1, 2, 3, 4, 5, 6, and you're good to go.
Great story from the Board of Elections down in the big city south of me.
One time, there was a big argument.
They set up a spreadsheet like this and I don't…the Democrats wanted them to press F9 6 times and the Republicans wanted to press it 9 times, and I said, look, it doesn't matter.
It's all random every single time, but let's just come up with some nice little policy, and, every month, we press it N times, and you're good to go.
Now, in real life, you know, so I have extra columns here that we don't need.
I'm going to go to the next sheet where I actually combined everything all into one big formula.
VLOOKUP.
Here's the small of A2.
Rather than type the number 1, I use the row of A1.
That will change to the number 2 in the next row and then the VLOOKUP.
So, it all just becomes one nice little formula.
They press F9 and they get a new person.
Abby leaves, don’t delete her row.
Just press DELETE to delete that cell and now Abby will never be chosen.
New person comes on board, well, you could put them where Abby was, NEWGUY, or you could put them down here at the bottom, NEWGAL, alright, and see, now, they're eligible to be selected.
So, we'll press F9 and, eventually, NEWGUY showed up there, NEWGAL shows up there, so they become part of the group to be selected every time.
[ =VLOOKUP(SMALL(A$2,A$101,ROW(A1)),$A$2:$B$101,2,FALSE) ] So, interesting little workbook.
If you need to do this, just shoot me a note, bill@mrexcel.com, I'll send you the workbook, and you can use this.
It works form anywhere from, well, 1 employee up to a 100 employees, and, of course, it would be easy to customize it for more just by changing that VLOOKUP.
So, interesting little use for Excel there.
Combined a couple of different functions RAND, small, small and VLOOKUP [ unintelligible – 04:39 ].
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1491: Random Drug Testing.
This is a cool question sent in.
Small company of 26 employees, 26 employees, and every month, they want to randomly select 2 of those employees to be drug tested and they want to use Excel to do that, and they said, well, right now, we have 26 but we might have more or less employees in the future so we want to be able to have this grow.
So, let's see.
Took the whole spreadsheet and changed it to be one certain color.
Took the range where they're going to put the employee names and did no fill there so that way they know where they're allowed to enter those values.
Then, to the left, over here in column A…and I made it a real faint grey so that way no one would be changing it, and put a nice little formula that says…well, here.
Let's look at it up here.
We're using just =RAND().
That gives us a random decimal between 0 and 1, but then I said, hey, if B2 is blank, if that employee, if there's no employee to the right of me, then use a really large number.
In this case, 2 is a huge number because it's larger than RAND will ever give us.
We want to make sure those blank cells never show up in the next part, alright?
So, every time we press F9, out there on the left hand side, we're getting a new set of random numbers.
Down here where there's no employees, where there might be future employees, we're getting just the number 2 all the time, alright.
[ =IF(ISBLANK(B2),2,RAND()) ] Now, here's what I'm doing.
I put the 3 numbers, 1, 2, 3.
I know they asked for 2 -- I wanted to show you how it works for 3 though -- and then I ask for the small of A $ sign 2, A $ sign 101, that whole list of employees , D4.
So, the small of that range , 1 gives me the smallest value.
That's the same as MIN.
No arguments there, but small is really good because now it will give me the second smallest value and the third smallest value and so on.
Well, in this case, I’m only going for 3.
[ =SMALL(A$2,A$101,D6) ] Now that I know these numbers, I'm going to use VLOOKUP to go figure out which employee is associated with that number, and it might just be me being superstitious but I made sure that all this flows left to right.
That way, Excel calculates the random numbers over in A first, then it calculates the small here, and before it has a chance to go back and recalculate the random numbers in A, it does the VLOOKUP.
So, I’m going left to right.
So, VLOOKUP of that small number over here in A2 to B102, I want the second column, and I of course want an exact match.
So, every time I press F9, we're getting a new set of employees.
[ =VLOOKUP(E4,$A$2:$B$101,2,FALSE) ].
When you open the file, it's going to recalculate.
So, of course, just as soon as you open the file, you're going to have some random set.
I suggested…I realized this is…every single one is random.
Just come up with a policy that says we're going to open the file, we're going to press F9 6 times, and whoever's up there then at that point are the people.
So, open it, 1, 2, 3, 4, 5, 6, and you're good to go.
Great story from the Board of Elections down in the big city south of me.
One time, there was a big argument.
They set up a spreadsheet like this and I don't…the Democrats wanted them to press F9 6 times and the Republicans wanted to press it 9 times, and I said, look, it doesn't matter.
It's all random every single time, but let's just come up with some nice little policy, and, every month, we press it N times, and you're good to go.
Now, in real life, you know, so I have extra columns here that we don't need.
I'm going to go to the next sheet where I actually combined everything all into one big formula.
VLOOKUP.
Here's the small of A2.
Rather than type the number 1, I use the row of A1.
That will change to the number 2 in the next row and then the VLOOKUP.
So, it all just becomes one nice little formula.
They press F9 and they get a new person.
Abby leaves, don’t delete her row.
Just press DELETE to delete that cell and now Abby will never be chosen.
New person comes on board, well, you could put them where Abby was, NEWGUY, or you could put them down here at the bottom, NEWGAL, alright, and see, now, they're eligible to be selected.
So, we'll press F9 and, eventually, NEWGUY showed up there, NEWGAL shows up there, so they become part of the group to be selected every time.
[ =VLOOKUP(SMALL(A$2,A$101,ROW(A1)),$A$2:$B$101,2,FALSE) ] So, interesting little workbook.
If you need to do this, just shoot me a note, bill@mrexcel.com, I'll send you the workbook, and you can use this.
It works form anywhere from, well, 1 employee up to a 100 employees, and, of course, it would be easy to customize it for more just by changing that VLOOKUP.
So, interesting little use for Excel there.
Combined a couple of different functions RAND, small, small and VLOOKUP [ unintelligible – 04:39 ].
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
