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,

[email protected], 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.

Thanks Bill … I need to keep SMALL, ISBLANK, and RAND() in mind. These are not functions I use everyday.

Very cool Bill. I'll have to try the SMALL function. Thanks!

Thanks Bill

wouldnt it be ALOT easier using the Mrand function (morefunc. add in)?

This worked perfectly thank you for ther help

Can you do this for a random pull of 50% all employees in the pool over four quarters?

Bill can you send me this worksheet

Bill can you send me this worksheet? Thanks

I enjoyed your podcast… I would like copy of the workbook…if you could send to [email protected]

Please send info TIP sheet please.

Hi bill could you send me this work book please.

Hi Bill, can you send me the workbook?

Awesome idea, just did it, works great, thanks

Any chance you can send me this workbook ?

Hello Bill – how can I increase or reduce the % of random pulls in my total pool?

AWESOME video and workbook! This is exactly what we were looking for! I do have a few questions though. For example what if my list is longer than 100 employees that need to be included? Can you do percentages? We need to be able to pull 10% for alcohol and 30% for drug testing. We're a new consortium and are trying to figure out a good way to pull all of our randoms.

Hi Bill, can you please send me this workbook please. I have appx 100 employees to randomly select for drug testing. Thanks.

How do I change the percentage needed to pull from 20% to 50% using your spreadsheet?

I need to integrate weekly with clients needing to be 1x 2x 3x + per week with the ability to eliminate them if they are chosen for that day.