Math & Trig
Group rows into fixed-size batches (1,1,1, 2,2,2) and alternating N-row bands
Assign cohort/pallet batch IDs every N rows, cycle 1..N slot positions within each batch, and shade alternating N-row blocks.
Integer division (QUOTIENT) + remainder (MOD): the universal N-row batching pair
Warehouse packing lists group every 3 items onto one Pallet ID (1,1,1, 2,2,2, 3,3,3) with slot numbers (1,2,3, 1,2,3) inside each pallet—and financial schedules often shade alternating 3-row or 4-row quarterly blocks for readability.
Starting from zero-based row offset idx = ROW(A2) - ROW($A$2) (0, 1, 2, 3, 4, 5...), three clean formulas emerge for batch size N ($E$2): BatchID = QUOTIENT(idx, N) + 1; SlotInBatch = MOD(idx, N) + 1; and AlternatingBandFlag = MOD(QUOTIENT(idx, N), 2) = 1 (TRUE on even-numbered batches 2, 4, 6...).
The preview explicitly supplies consecutive zero-based indices 0 through 6 in A2:A8. Batch size must be a positive whole number. Batching is by record position, not pallet capacity, weight or an inferred customer cohort. The shade formula returns a flag; it does not install a formatting rule.
| Zero-Based idx | Batch ID (N=3) | Slot Within Batch (1..3) | Alternating 3-Row Band? |
|---|---|---|---|
| 0, 1, 2 (Rows 2–4) | 1, 1, 1 | 1, 2, 3 | FALSE (Unshaded Band 1) |
| 3, 4, 5 (Rows 5–7) | 2, 2, 2 | 1, 2, 3 | TRUE (Shaded Band 2!) |
| 6 (Row 8) | 3 | 1 | FALSE (Unshaded Band 3) |
Batch ID, within-batch slot, and alternating N-row band formulas
Evaluated for item index A6 (zero-based offset 4, the 5th item) with batch size $E$2 = 3: Batch = 2, Slot = 2, Shaded = TRUE.
=QUOTIENT(A6, $E$2) + 1Within-batch slot position (1..N) and alternating N-row shade flag
MOD(idx, N) + 1 cycles 1, 2, ..., N cleanly; MOD(QUOTIENT(idx, N), 2) = 1 toggles every N rows.
=MOD(QUOTIENT(A6, $E$2), 2) = 1Slot within the batch
For zero-based index 4 and a batch of 3, the slot is 2.
=MOD(A6,$E$2)+1Try the example
Use the original values or change them to test your understanding.