Math & Statistical
Generate Repeating Block Numbers Every N Rows (1,1,1, 2,2,2)
Assign repeating pallet, carton, or cohort group IDs (1, 1, 1, 2, 2, 2...) and within-block slot numbers (1, 2, 3, 1, 2, 3...) every N rows using INT and MOD.
Batching sequential rows into fixed-size cartons, pallets, or breakout groups!
Warehouse packing manifests, clinical randomization lists, and classroom rosters constantly need to group sequential items into fixed blocks of N rows: items 1–3 go into Carton 1, items 4–6 go into Carton 2, and items 7–9 go into Carton 3! Dividing the zero-based row offset (ROW(A2) - ROW($A$2)) by block size N inside INT((ROW(A2) - ROW($A$2)) / $E$2) + 1 produces 1, 1, 1, 2, 2, 2, 3, 3, 3 cleanly for any positive integer N!
Pair that block ID with MOD(ROW(A2) - ROW($A$2), $E$2) + 1 to label the exact slot inside each carton (Slot 1, Slot 2, Slot 3) and guard against zero or negative block sizes with IF($E$2 < 1, "Invalid Block Size", ...)!
| Item Row (Zero-Based Offset k) | Block ID: INT(k / 3) + 1 | Slot in Block: MOD(k, 3) + 1 | Packing Label |
|---|---|---|---|
| Rows 2–4 (k = 0, 1, 2) | 1, 1, 1 | 1, 2, 3 | Carton 1 — Slots 1..3 |
| Rows 5–7 (k = 3, 4, 5) | 2, 2, 2 | 1, 2, 3 | Carton 2 — Slots 1..3 |
| Row 8 (k = 6) | 3 | 1 | Carton 3 — Slot 1 |
Repeating Block / Group Number Every N Rows
Computes the 1-based group number for the 7th item (A8, offset 6 from A2) when block size N is stored in E2.
=IF($E$2 < 1, "Invalid Block Size", INT((ROW(A8) - ROW($A$2)) / $E$2) + 1)Within-Block Position Index (1 through N)
Returns the 1-based slot index inside the current N-row block using MOD.
=IF($E$2 < 1, "Invalid Block Size", MOD(ROW(A6) - ROW($A$2), $E$2) + 1)Try the example
Use the original values or change them to test your understanding.