Design Goals

Given a spreadsheet containing application information, our goal is to randomly assign 2 reviewers (out of $N$) to each distinct application. We also want to do this in a way that is robust to reordering applications in the sheet, changing or replacing cells, etc., so that the assignments remain stable.

The easy way to achieve this would be to use the application ID as the seed to a random number generator, and then use that RNG to select reviewers. Unfortunately, Excel’s RAND function does not support explicit seeds, so we can’t do that.

Enter the LCG

If Excel doesn’t give us a seedable RNG, can we make one? Yes!

A linear congruential generator is one of the simplest pseudorandom number generator designs, and it’s simple enough to be implemented as Excel formulas. The basic ideas is to start with a seed $X_0$, and then compute subsequent numbers by $X_i = (a X_{i-1} + c) \bmod m$. It isn’t a great random number generator, but it’s good enough for what we need.

Using the application number as the seed, instead of just generating a number for each application, makes our assignments stable: given an application number, we can directly compute its assignments.

Unfortunately, it isn’t quite that simple. An LCG on its own outputs numbers in the range $[0,m)$, but we only have $N$ reviewers. To select two reviewers, we need both the two random numbers, and we need to convert them into the range $[0,n)$ (and for the second, skip over the reviewer we picked in the first step).

To implement this all of this, our workbook has two sheets: the applicant info sheet and the “AssignRNG” assignment worksheet. “AssignRNG” implements the LCG, split out into different columns of a table (RVRNG) that allow us to inspect the LCG’s operation, and compute each of the reviewers.

On the right, you’ll find the parameters, with named cells holding their values (i.e., $m$ is in the cell P.m). These parameters are:

• $m$: the LCG modulus
• $a$: the LCG multiplier
• $c$: the LCG increment / additive constant
• off ($G$): an additive offset for converting entry numbers to seeds
• mult ($H$): a multiplier for converting entry numbers to seeds

The first three parameters are the core LCG parameters. Their values can be a bit finicky; Excel has limited numeric range and precision, so the values used by many implementations are too large for accurate generation in Excel. The Wikipedia article has a table of values from different implementations. These are from random0, an old implementation with a low period (the generator will start repeating more quickly than you would want for a lot of purposes), but they are in-range for Excel and good enough for our purposes, especially since we are only generating two random numbers from each seed.

The last two parameters are not LCG parameters, but control the seed generation process. This control allows us to change the randomization each year. The right portion of the sheet also contains the list of reviewers, counts of the number of times each is assigned in the RNG table, and counts of how often each pair of reviewers is assigned. These counts allow lightweight randomization checks.

The RNG itself is a table (named RVRNG) with 7 columns:

1. Index is the row number in the table. Each application in the applications sheet is assigned a sequential application number, starting with 1; we look up that number in this column to determine that application’s assignment.

2. V1 and V2 are the first and second random numbers drawn from an LCG seeded with the Index. They are computed as follows:

   $$\begin{align*} s & = G (i + H) \\ V_1 & = (a s + c) \bmod m \\ V_2 & = (a V_1 + c) \bmod m \\ \end{align*}$$

3. I1 and I2 are the first and second reviewer indices. They are computed by $I_1 = V_1 \bmod N$ and $I_2 = V_2 \bmod (N-1)$. We use $N-1$ as the second modulus because we have already used one reviewer in the first selection, so we need to select from the remaining reviewers.

4. R1 selects the first reviewer, by using $I_1 + 1$ to pick a reviewer from the list of reviewers.

5. R2 selects the second reviewer, by using $I_2 + k$ to pick a reviewer from the list of reviewers, where $k=1$ if $I_2 < I_1$ and $k=2$ otherwise, to skip $I_1$ in the list.

Together, those cells select a pair of reviewers from the list, ensuring the same reviewer is not selected twice.

Mapping to Applications

Back in the application tracker, we each application has an application number, assigned when it is added to the spreadsheet and never changed. We then look up the assignment for that application number, and include it in the sheet. To do this in “wide” format, where each reviewer has a column that gets marked “X” for applications assigned to them, we use the following formula (assuming the reviewer column is E):

=IF(OR(VLOOKUP([@Entry], RVRNG, 6, FALSE)=E$1,
       VLOOKUP([@Entry], RVRNG, 7, FALSE)=E$1),
    "X", "")

Final Remarks

I’ve been through a couple of iterations to get to this point, but it seems to be a pretty robust and solid way to assign reviewers within Excel, without needing to import assignments from other tools. So long as application numbers are not changed, assignments are stable as the application table is sorted, cells are changed (e.g., replacing the “X” with reviewer comments), and most other manipulations.

Hope this is helpful! The Excel sheet is available here (local download).