Solved

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

Posted on 2011-03-09
7
251 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:fredgcook
  • 4
  • 2
7 Comments
 
LVL 12

Expert Comment

by:Alan3285
ID: 35087905
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
 
LVL 12

Accepted Solution

by:
Alan3285 earned 500 total points
ID: 35088057
Hi,

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

HTH,

Alan.
Random-Drug-Tests---Version-001.xls
0
 

Author Closing Comment

by:fredgcook
ID: 35088486
Thanks
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 32

Expert Comment

by:aleghart
ID: 35088643
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
 
LVL 12

Expert Comment

by:Alan3285
ID: 35088696
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
 
LVL 32

Expert Comment

by:aleghart
ID: 35089216
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
 
LVL 12

Expert Comment

by:Alan3285
ID: 35089365
LOL!

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

;-)

Alan.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword (May 2015) This web page has appeared at Google.  It's definitely worth considering! https://www.google.com/about/careers/students/guide-to-technical-development.html How to Know You are Making a Difference at EE In August, 2013, one …
Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question