Learn Excel 2010 – “Random Drug Testing”: Podcast #1491

Learn Excel 2010 – “Random Drug Testing”: Podcast #1491


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.

19 thoughts on “Learn Excel 2010 – “Random Drug Testing”: Podcast #1491

  1. 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.

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

  3. 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.

Leave a Reply

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