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
269 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

724 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