Assign People or Leads to K Balanced Teams (Round-Robin MOD vs Block)

Balance headcount, not skill or workload

Independent random assignments do not guarantee balanced team sizes; volatile random formulas can also change when recalculated. These deterministic formulas use row position instead. Sorting or inserting roster rows changes assignments, so preserve the final assignments as values when they must remain fixed.

For this six-person example, enter an integer team count from 1 to 6 in D2. Fill every roster cell A2:A7. Repeated names count as separate rows; check duplicates separately. Balancing headcount does not guarantee balanced skills, effort, or capacity.

Round-robin assignment

Enter this formula in C2 and fill down to C7. With three teams, assignments are 1, 2, 3, 1, 2, 3.

=IF(NOT(ISNUMBER($D$2)),"Invalid Team Count",IF(OR($D$2<1,$D$2>ROWS($A$2:$A$7),$D$2<>INT($D$2)),"Invalid Team Count",IF(COUNTBLANK($A$2:$A$7)>0,"Complete the roster","Team "&(MOD(ROW(A2)-ROW($A$2),$D$2)+1))))

Balanced contiguous assignment

Enter this formula in E2 and fill down to E7. QUOTIENT multiplies the zero-based row offset by the team count before dividing by the roster size. This keeps adjacent members together and makes team sizes differ by at most one. With six people and four teams, assignments are 1, 1, 2, 3, 3, 4: headcounts 2, 1, 2, 1.

Dividing the row offset by CEILING(N/K) is not sufficient: six people and four teams would produce headcounts 2, 2, 2, 0. The corrected formula uses every requested team when K is no larger than N.

For a different roster size, update every absolute roster range and fill only the actual roster rows. Blank rows are rejected instead of silently becoming team members.

=IF(NOT(ISNUMBER($D$2)),"Invalid Team Count",IF(OR($D$2<1,$D$2>ROWS($A$2:$A$7),$D$2<>INT($D$2)),"Invalid Team Count",IF(COUNTBLANK($A$2:$A$7)>0,"Complete the roster","Team "&(QUOTIENT((ROW(A2)-ROW($A$2))*$D$2,ROWS($A$2:$A$7))+1))))

Further reading

• Microsoft Support — MOD function