How to determine the probability an employee from an office will be selected for a random drug test using Excel?

We have 66 total employees:  42 in office A, 16 in office B, 6 in office C, and 2 in office D.  We are discussing implementing monthly or quartery random drug tests and want to know the probability that an employee would be chosen from a particular office.  We may have thought about this so much that we have confused ourselves, but basically we want to know that if we choose one employee out of the total of 66, what would be the chance the employee would come from office A, B, C, or D.  We would then like to know how it would change if we pick two employees each month.  Thanks for your help.
fredgcookAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
AlanConnect With a Mentor ConsultantCommented:
Hi,

For two employees, I think the attached works it out for you.

HTH,

Alan.
Random-Drug-Tests---Version-001.xls
0
 
AlanConsultantCommented:
Probability of each office for one employee is:

Office A = 42 / 66 = 63.6%
Office B = 16 / 66 = 24.2%
Office C = 6 / 66 = 9.1%
Office D = 2 / 66 = 3.0%

The percentages are rounded to 1 dp so they don't add up to exactly 100%

HTH,

Alan.
0
 
fredgcookAuthor Commented:
Thanks
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
aleghartCommented:
Here's what I figured as the chances of drawing at least one name from any office.  The total of odds increases beyond 100% on drawing 2 names.

 probability-draw2from66.xls
0
 
AlanConsultantCommented:
Hi aleghart,

Happy to admit I could be wrong, but I think the probability that, for example, Office A is chosen at least once, is 87.13%

What do you mean that the odds 'increases beyond 100% on drawing 2 names'?  Probabilities (P) are always such that:

0% <= P <= 100%

Something cannot be 101% likely to happen.

Alan.
0
 
aleghartCommented:
Sorry. You're right.  My formula is wrong on 2 draws.

> 0% <= P <= 100%

I got the prob of at least one hit in two draws
A = 87.13
B = 42.89
C = 17.48
D = 6.01
That's >100%

I was explaining that the total of those probabilities is higher than 100% because you are looking at multiple draws, not a single event.

But, consider my math education stopped 20 years ago...I'll defer to someone less rusty.
 probability-draw2from66.xls
0
 
AlanConsultantCommented:
LOL!

Mine stopped 20 years ago too, so perhaps we are both wrong!

;-)

Alan.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.