random numbers
Example: I have 1,036 nominees and each one has to be interviewed by 3
different people out of a group of 20 interviewers. 1,036/20 = 51.8 nominees per interviewer 1,036-(51*20)=16 So 16 people would have 52 nominees and 4 would have 51 nominees. (!6*52)+4*51=1,036 And I can figure all this out ahead of time. So I use INT(RAND()*20) in 3 columns and 1036 rows. Of course some rows will have duplicates but I can, in another 3 columns look at the rows with duplicates and assign another random number to replace the duplicate, no big deal. In my test case I did this two times and afterward only had 3 rows left with duplicates. The problem is that INT(RAND()*20) has no idea that it can only have 51 zeros, 51 ones, 51 twos, etc or 52 instead of the 51, I can manually adjust for the 4 51s and 16 52s. In my test case here is what happened 165 zeros 158 ones 190 twos 150 threes 167 fours 142 fives 157 sixes 166 sevens 159 eights 147 etc 170 154 160 141 152 154 154 135 143 147 ----- 3111 total random numbers assigned, I only wanted 1,036. Not cool. So I have to find something better or someway to limit my command with another command while at the same time leaving the randomness. Any ideas? |
random numbers
There are 560 unique combinations of 20 people taken 3 at a time.
I would generate those combinations and then make a copy for a total of 1120. Then I would assign the list of 1036 to these. Put the 20 names in B1 to U1 Then in row 1 to 1121, put a 1 below each person that would interview a the candiate in that row. then to get a list for each interviewer, you could use an autofilter. This sounds like a scheduling nightmare, however. It seems like you would probably want to organize 6 teams with two floaters, or 5 teams of 4 with one person being designated as a backup for each team. Also, your initial analysis is off. You need to conduct 3*1036 interviews, so each person would need to do (3*1036)/20 = 155.4 interviews, not 52/51 I can generate the 560 combinations if you need it. -- Regards, Tom Ogilvy "Dick Hassler" wrote in message ... Example: I have 1,036 nominees and each one has to be interviewed by 3 different people out of a group of 20 interviewers. 1,036/20 = 51.8 nominees per interviewer 1,036-(51*20)=16 So 16 people would have 52 nominees and 4 would have 51 nominees. (!6*52)+4*51=1,036 And I can figure all this out ahead of time. So I use INT(RAND()*20) in 3 columns and 1036 rows. Of course some rows will have duplicates but I can, in another 3 columns look at the rows with duplicates and assign another random number to replace the duplicate, no big deal. In my test case I did this two times and afterward only had 3 rows left with duplicates. The problem is that INT(RAND()*20) has no idea that it can only have 51 zeros, 51 ones, 51 twos, etc or 52 instead of the 51, I can manually adjust for the 4 51s and 16 52s. In my test case here is what happened 165 zeros 158 ones 190 twos 150 threes 167 fours 142 fives 157 sixes 166 sevens 159 eights 147 etc 170 154 160 141 152 154 154 135 143 147 ----- 3111 total random numbers assigned, I only wanted 1,036. Not cool. So I have to find something better or someway to limit my command with another command while at the same time leaving the randomness. Any ideas? |
random numbers
"Dick Hassler" wrote...
Example: I have 1,036 nominees and each one has to be interviewed by 3 different people out of a group of 20 interviewers. 1,036/20 = 51.8 nominees per interviewer No, every interviewer would have 3 times this many nominees if each nominee needs to be interviewed by 3 different interviewers. If you want as many different interviewer groups as possible, then create a list of of all groups of 3 interviewers out of 20 interviewers. There are more than enough of these since COMBIN(20,3) = 1140 1036. In a 3 col by 1140 row range (I'll use A1:C1140) enter A, B and C in each of the top row's cells. Then enter these formulas in the second row. A2: =IF(B1<"S",A1,CHAR(CODE(A1)+1)) B2: =IF(C1<"T",B1,CHAR(CODE(B1)+1)) C2: =CHAR(CODE(IF(C1<"T",C1,B2))+1) Select A2:C2 and fill down into A3:C1140. Then enter =RAND() in each cell in D1:D1140. Sort A1:D1140 on column D (ascending or descending order doesn't matter). Take the top 1036 of these. Assign one set of 3 interviewers to each nominee. Scheduling these is a whole different kettle of fish. |
random numbers
"Tom Ogilvy" wrote...
There are 560 unique combinations of 20 people taken 3 at a time. Last time I checked, COMBIN(20,3) was 1140 rather than 560. |
random numbers
"Harlan Grove" wrote...
.... Select A2:C2 and fill down into A3:C1140. . . . Forgot a step. At this point you need to select A1:C1140, copy, and paste special as values on top of itself. The the combinations won't be fubarred when sorted. |
random numbers
yep, combin(16,3) = 560
Guess I made a mistake on the number of interviewers and the OP's analysis. -- Regards, Tom Ogilvy Harlan Grove wrote in message ... "Tom Ogilvy" wrote... There are 560 unique combinations of 20 people taken 3 at a time. Last time I checked, COMBIN(20,3) was 1140 rather than 560. |
random numbers
Why sort anyway?
-- Regards, Tom Ogilvy Harlan Grove wrote in message ... "Harlan Grove" wrote... ... Select A2:C2 and fill down into A3:C1140. . . . Forgot a step. At this point you need to select A1:C1140, copy, and paste special as values on top of itself. The the combinations won't be fubarred when sorted. |
random numbers
"Tom Ogilvy" wrote...
Why sort anyway? .... You're right - no need as long as the assignment of letters to interviewers is arbitrary. Adding one shuffle of either interviewers or nominees won't hurt, and it does ensure the assignment of interviewers to nominees isn't purely a function of their respective list orders. |
All times are GMT +1. The time now is 01:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com