I need to randomly select 25% of workforce for Drug Screening. I have set up a template to test my formula out - 25 employees and used the rand as well as the randbetween formulas however it often creates duplicates - is there a simple formula to calculate without the duplication?
Rand selection without duplication...
- Thread starter bfurzland
- Start date
SirJB7
Excel Rōnin
Hi, bfurzland!
I think Luke M intended to write this:
"In C1 type and copy down thru C25:
=INDEX(A$1:A$100,MATCH(LARGE(B$1:B$100,ROW(A1)),B$1:B$100,0))
And then get the 25% names in C1:C25.
Note that each time you worksheet is recalculated, list in C will change.
"
Give a try with this modification.
Regards!
I think Luke M intended to write this:
"In C1 type and copy down thru C25:
=INDEX(A$1:A$100,MATCH(LARGE(B$1:B$100,ROW(A1)),B$1:B$100,0))
And then get the 25% names in C1:C25.
Note that each time you worksheet is recalculated, list in C will change.
"
Give a try with this modification.
Regards!
Here is what happens when I apply this, adjusted for, to a test template of 25 employees. COL C = RAND() COL D = INDEX(B$2:B$21,MATCH(LARGE(C$2:C$21,ROW(B2)),C2:C$21,0))
COL A = Number, COL B=Employee...,COL C=RAND(), COL D=INDEX
1 Employee A 0.501143691412801 Employee I
2 Employee B 0.290204036614349 Employee M
3 Employee C 0.896169584304520 Employee P
4 Employee D 0.241156440065401 Employee N
5 Employee E 0.366805921168875 #N/A
6 Employee F 0.418678449707734
7 Employee G 0.380704541835210
8 Employee H 0.474238256950862
9 Employee I 0.812715255113189
10 Employee J 0.405617968593719
11 Employee K 0.412548305048727
12 Employee L 0.452885536010861
13 Employee M 0.403825997001210
14 Employee N 0.804755766787718
15 Employee O 0.385394887656361
16 Employee P 0.396301368717947
17 Employee Q 0.764474645827894
18 Employee R 0.788568296295631
19 Employee S 0.064568656880134
20 Employee T 0.418609774914165
* Contract stipulates that 25% of the workforce be tested every month.
It appears that the fist employee provided by the formula is not even one of the top 5... I must be doing something wrong - you know how sometimes you have looked at something so long it no longer makes sense, 1 + 1 is 3..., or you have more than 1 Monday in a week - well that's my Friday... What am I missing?
COL A = Number, COL B=Employee...,COL C=RAND(), COL D=INDEX
1 Employee A 0.501143691412801 Employee I
2 Employee B 0.290204036614349 Employee M
3 Employee C 0.896169584304520 Employee P
4 Employee D 0.241156440065401 Employee N
5 Employee E 0.366805921168875 #N/A
6 Employee F 0.418678449707734
7 Employee G 0.380704541835210
8 Employee H 0.474238256950862
9 Employee I 0.812715255113189
10 Employee J 0.405617968593719
11 Employee K 0.412548305048727
12 Employee L 0.452885536010861
13 Employee M 0.403825997001210
14 Employee N 0.804755766787718
15 Employee O 0.385394887656361
16 Employee P 0.396301368717947
17 Employee Q 0.764474645827894
18 Employee R 0.788568296295631
19 Employee S 0.064568656880134
20 Employee T 0.418609774914165
* Contract stipulates that 25% of the workforce be tested every month.
It appears that the fist employee provided by the formula is not even one of the top 5... I must be doing something wrong - you know how sometimes you have looked at something so long it no longer makes sense, 1 + 1 is 3..., or you have more than 1 Monday in a week - well that's my Friday... What am I missing?
Faseeh
Excel Ninja
Hi all,
I would like to add something on the concept of sampling that i think can simplify the problem. Since there is no specific type of sampling technique mentioned by bfurzland therefore i assume it is at our discretion how do we handle the data.
Actually we can use 'Quota or Strata Sampling' technique to breakup the no. of employees in the small group and then select randomly from that group. The advantages that can be achieved is that:
1. There is no duplication for sure.
2. The collected sample is evenly distributed.
So we start first with the total no. of employees in a firm and the %age check needed. Once these two values in hand we can calculate how many employees are going to be tested and then make equivalent no. of categories and selecting just one from each of them. This will ensure that only one employee is selected from each group and employees are selected from every group.
..Not a big deal when comes to excel formulas but i want you to examine the 'conceptual frame work' in that.
Sample workout from me: http://dl.dropbox.com/u/60644346/Cluster.xlsx
Wikipedia link for Sampling: http://en.wikipedia.org/wiki/Sampling_%28statistics%29
Thank You,
Faseeh
I would like to add something on the concept of sampling that i think can simplify the problem. Since there is no specific type of sampling technique mentioned by bfurzland therefore i assume it is at our discretion how do we handle the data.
Actually we can use 'Quota or Strata Sampling' technique to breakup the no. of employees in the small group and then select randomly from that group. The advantages that can be achieved is that:
1. There is no duplication for sure.
2. The collected sample is evenly distributed.
So we start first with the total no. of employees in a firm and the %age check needed. Once these two values in hand we can calculate how many employees are going to be tested and then make equivalent no. of categories and selecting just one from each of them. This will ensure that only one employee is selected from each group and employees are selected from every group.
..Not a big deal when comes to excel formulas but i want you to examine the 'conceptual frame work' in that.
Sample workout from me: http://dl.dropbox.com/u/60644346/Cluster.xlsx
Wikipedia link for Sampling: http://en.wikipedia.org/wiki/Sampling_%28statistics%29
Thank You,
Faseeh