# Randomly assign an application to 3 reviewers?

1. ## Randomly assign an application to 3 reviewers?

Can someone suggest a formula to assign 273 applicants to 10 reviewers, where each application must be reviewed by 3 different reviewers (where the reviewers are not the same group of 3 each time). I have searched the forum and found a similar question but the posted answer was to specific to that post so any help would be very appreciated!

2. ## Re: Randomly assign an application to 3 reviewers?

Here is an idea
Tabla.png
Where B2 is =RANDBETWEEN(1,10)
C2 is =IF(B2<10,B2+1,B2-RANDBETWEEN(2,3))
D2 is =IF(C2<10,C2+1,C2-RANDBETWEEN(2,3))
And copy row 2 down
Cheers
Sergio

3. ## Re: Randomly assign an application to 3 reviewers?

If I worked for you then with Sergiomabres's approach I would definitely volunteer to be Reviewer #1. The distribution of applicants between reviewers is distinctly uneven. Reviewer #8 typically gets about four times the applicants that Reviewer #8 gets.
Of course this could be considered a feature - you could make reviewer #8 your most competent or least busy assessor and make interviewer #1 your least able or most busy assessor.

Here is a typical frequency distribution:

s-frequencies.png

Here is an alternate approach that evenly allocates applicants between reviewers. With reference to the attached workbook the 273 combinations of 3 reviewers are located in the range F1:H274. Sergio's approach, for comparison, has been implemented in B1:D274.

There are just 10!/(3!*(10-3)! = 120 combinations of 3 out of 10 reviewers. You have 273 applicants, so twice around the 120 combinations deals with the first 240 applicants. For the remaining 33 applicants if the goal is to maintain an even distribution of applicants among reviewers then we can't just take a block of 33 entries from the combinations table - that would be heavily biased. Instead for each of the last 33 applicants I randomly choose 1 of the possible 120 combinations of 3 reviwers. A constraint here is that we need to make sure to choose all 3 reviewers from the same specific combination without random number recalculation causing us to pick up the three reviewers each from different combinations. This is done by selecting the three column range F242:H242 and then entering as an array formula with CTRL-SHIFT_ENTER:
Formula:
`Please Login or Register  to view this content.`

Copying F242:H242 down to row 274 covers each of the last 33 applications.

Here is a typical frequency distribution resulting from this approach:

g-frequencies.png

In the attached workbook F9 (re-calculate) can be pressed to update both frequency distribution charts.

4. ## Re: Randomly assign an application to 3 reviewers?

That is impressive! Thanks Geof, using your test sheet and using your input let me refrain my formulas
B2 =RANDBETWEEN(1,10)
C2 =IF(B2<10,B2+1,1)
D2 =IF(C2<10,C2+1,1)
This set get a much better distribution
Cheers
Sergio

5. ## Re: Randomly assign an application to 3 reviewers?

Yeah, your new formulas have no systematic bias at all. Well done!

My approach has a problem that yours does not in that if the user starts at the top of the list and begins to make assignments in sequential order then under my scheme reviewer #1 is going to get pretty tired pretty quickly!

Hopefully the OP likes your latest solution!

Geoff

6. ## Re: Randomly assign an application to 3 reviewers?

Sergio and Geoff, thank you very very much! You help is appreciated and I am grateful for the opportunity to learn! It works perfectly!

7. ## Re: Randomly assign an application to 3 reviewers?

No problem, thanks for the feedback and the reputation points.

There are currently 1 users browsing this thread. (0 members and 1 guests)