Lookup & Reference
Randomly Assign Rows to Equal-Sized Balanced Groups (RANK + MOD + INDEX)
Eliminate lopsided RANDBETWEEN group sizes by ranking pre-drawn random decimals with RANK(B2, $B$2:$B$7) and cycling evenly through group labels via INDEX($E$2:$E$4, MOD(Rank - 1, K) + 1).
Why RANDBETWEEN(1, 3) creates lopsided cohorts—and how RANK + MOD guarantees exact balance!
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!
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(E2:E4) = 0, "Define Group Names", INDEX(E2:E4, MOD(RANK(B2, B2:B7) - 1, COUNTA(E2:E4)) + 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(E2:E4) = 0, "Define Group Names", INDEX(E2:E4, MOD(RANK(B3, B2:B7) - 1, COUNTA(E2:E4)) + 1))Try the example
Use the original values or change them to test your understanding.