Lookup & Reference
Randomly Assign Rows to Equal-Sized Balanced Groups (RANK + MOD + INDEX)
Use frozen random draws, a deterministic tie breaker and round-robin assignment to balance example groups; size differences are at most one.
Balance cohorts using rank, a tie breaker and modulo
When you assign 6 participants to 3 cohorts using =INDEX($E$2:$E$4, RANDBETWEEN(1, 3)), independent draws routinely put 4 people in Cohort_A, 2 in Cohort_B, and 0 in Cohort_C—and recalculate every time any cell changes. Instead, freeze a one-time random seed column B2:B7 (Paste Values from RAND()) and convert each row’s unique rank 1..N into a perfectly balanced round-robin cycle 1..K. RANK alone assigns equal ranks to tied draws, so add COUNTIF($B$2:B2,B2)-1 as a row-order tie breaker. The group-name list must be contiguous. If N is not divisible by K, group sizes differ by at most one. This illustrative worksheet is not a secure or concealed clinical-trial randomization system.
Specifically, MOD(RANK(B2, $B$2:$B$7) - 1, COUNTA($E$2:$E$4)) + 1 maps ranks {1, 2, 3, 4, 5, 6} cleanly onto {1, 2, 3, 1, 2, 3}, guaranteeing every cohort receives N / K = 2 members.
| Participant & Frozen Random Draw B2:B7 | RANK(B, $B$2:$B$7) | MOD(Rank - 1, 3) + 1 | Assigned Balanced Cohort |
|---|---|---|---|
| Emp_4 (Draw = 0.93 — Highest) | Rank 1 | MOD(0, 3) + 1 = 1 | Cohort_A |
| Emp_1 (Draw = 0.81 — 2nd) | Rank 2 | MOD(1, 3) + 1 = 2 | Cohort_B |
| Emp_2 (Draw = 0.14 — 6th) | Rank 6 | MOD(5, 3) + 1 = 3 | Cohort_C |
Balanced Equal-Size Cohort Assignment via INDEX + MOD + RANK
Ranks draw B2 within B2:B7 and cycles modulo the count of group names in E2:E4.
=IF(COUNTA($E$2:$E$4) <> ROWS($E$2:$E$4), "Define Group Names", INDEX($E$2:$E$4, MOD(RANK(B2, $B$2:$B$7) + COUNTIF($B$2:B2, B2) - 2, COUNTA($E$2:$E$4)) + 1))Assigned Cohort for Lowest Random Draw Row (B3 = 0.14 -> Rank 6 -> Group 3)
Evaluates the same round-robin formula on row 3 (B3 = 0.14).
=IF(COUNTA($E$2:$E$4) <> ROWS($E$2:$E$4), "Define Group Names", INDEX($E$2:$E$4, MOD(RANK(B3, $B$2:$B$7) + COUNTIF($B$2:B3, B3) - 2, COUNTA($E$2:$E$4)) + 1))Try the example
Use the original values or change them to test your understanding.