+ Reply to Thread
Results 1 to 7 of 7

Randomly assign an application to 3 reviewers?

  1. #1
    Registered User
    Join Date
    02-27-2018
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    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. #2
    Registered User
    Join Date
    05-10-2016
    Location
    Cordoba, Argentina
    MS-Off Ver
    2016
    Posts
    27

    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. #3
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    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: copy to clipboard
    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.
    Attached Files Attached Files
    Last edited by GeoffW283; 02-02-2019 at 06:34 PM.

  4. #4
    Registered User
    Join Date
    05-10-2016
    Location
    Cordoba, Argentina
    MS-Off Ver
    2016
    Posts
    27

    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. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    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. #6
    Registered User
    Join Date
    02-27-2018
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    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. #7
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Randomly assign an application to 3 reviewers?

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help assigning reviewers to applications randomly
    By LCLEE00 in forum Excel General
    Replies: 3
    Last Post: 11-27-2017, 03:47 PM
  2. Assign players to teams randomly
    By buhaj47 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2015, 05:26 PM
  3. Randomly assign to groups, without duplicates
    By rw2 in forum Excel General
    Replies: 5
    Last Post: 05-08-2015, 08:31 AM
  4. [SOLVED] Randomly assign codes to cells
    By Gemsie in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-29-2013, 12:28 PM
  5. Randomly assigning pairs of reviewers to a list of files
    By thatspoetic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-22-2013, 08:52 AM
  6. Randomly assign a value to rows
    By covegolfer in forum Excel General
    Replies: 3
    Last Post: 06-13-2011, 12:29 PM
  7. [SOLVED] How do I randomly assign values in a grid?
    By Excel question in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2005, 08:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1