Combination
Systematic sample of invoices with a fixed start
Select every k-th invoice from a population: the interval comes from ROUNDDOWN and ROWS, SEQUENCE counts the picks from a fixed start typed in a cell, and INDEX lists the selected rows.
Functions used:INDEXSEQUENCEROUNDDOWNROWSExcel 2021 or later (This combination needs Excel 2021 or later)
Worked example
Select 4 sales invoices from a population of 24
Scenario
For the revenue testing of Veldhuis Installatie BV you have the sales invoices of 2025 from the XAF auditfile. You decide on a systematic sample of n = 4 invoices to vouch to the contract and the delivery note.
You draw the random start once (3, within the interval) and type it in G2, so a colleague or reviewer gets the same selection and the file documents how it was made.
The list here has 24 invoices so it fits the grid; in practice the population is thousands of invoices, for example 6,0006.000 invoices with n = 60 gives an interval of 100, and the same formula works unchanged.
The data
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice no. | Date | Customer | Amount | Sample size (n) | 44 | ||
| 2 | 25-0101 | 8 Jan 2025 | D1004 | €4,850.00€ 4.850,00 | Fixed start | 33 | ||
| 3 | 25-0102 | 22 Jan 2025 | D1001 | €12,300.00€ 12.300,00 | Selected invoices | 25-0103=INDEX(A2:A25, G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1, 0))=INDEX(A2:A25; G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1; 0)) | ||
| 4 | 25-0103 | 5 Feb 2025 | D1007 | €2,175.00€ 2.175,00 | 25-0109 | |||
| 5 | 25-0104 | 19 Feb 2025 | D1002 | €8,640.00€ 8.640,00 | 25-0115 | |||
| 6 | 25-0105 | 3 Mar 2025 | D1009 | €15,900.00€ 15.900,00 | 25-0121 | |||
| 7 | 25-0106 | 17 Mar 2025 | D1001 | €3,420.00€ 3.420,00 | ||||
| 8 | 25-0107 | 31 Mar 2025 | D1005 | €6,780.00€ 6.780,00 | ||||
| 9 | 25-0108 | 14 Apr 2025 | D1003 | €9,215.00€ 9.215,00 | ||||
| 10 | 25-0109 | 28 Apr 2025 | D1008 | €1,980.00€ 1.980,00 | ||||
| 11 | 25-0110 | 12 May 2025 | D1002 | €22,450.00€ 22.450,00 | ||||
| 12 | 25-0111 | 26 May 2025 | D1006 | €5,310.00€ 5.310,00 | ||||
| 13 | 25-0112 | 10 Jun 2025 | D1004 | €7,860.00€ 7.860,00 | ||||
| 14 | 25-0113 | 24 Jun 2025 | D1001 | €18,600.00€ 18.600,00 | ||||
| 15 | 25-0114 | 8 Jul 2025 | D1010 | €2,940.00€ 2.940,00 | ||||
| 16 | 25-0115 | 22 Jul 2025 | D1003 | €11,275.00€ 11.275,00 | ||||
| 17 | 25-0116 | 5 Aug 2025 | D1007 | €4,120.00€ 4.120,00 | ||||
| 18 | 25-0117 | 19 Aug 2025 | D1009 | €9,830.00€ 9.830,00 | ||||
| 19 | 25-0118 | 2 Sep 2025 | D1005 | €13,700.00€ 13.700,00 | ||||
| 20 | 25-0119 | 16 Sep 2025 | D1002 | €6,050.00€ 6.050,00 | ||||
| 21 | 25-0120 | 30 Sep 2025 | D1008 | €3,590.00€ 3.590,00 | ||||
| 22 | 25-0121 | 14 Oct 2025 | D1006 | €16,480.00€ 16.480,00 | ||||
| 23 | 25-0122 | 28 Oct 2025 | D1001 | €8,225.00€ 8.225,00 | ||||
| 24 | 25-0123 | 11 Nov 2025 | D1004 | €5,640.00€ 5.640,00 | ||||
| 25 | 25-0124 | 9 Dec 2025 | D1010 | €10,760.00€ 10.760,00 | ||||
| 26 |
Swipe sideways to see all columns
Formula in G3
=INDEX(A2:A25,G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1, 0))
=INDEX(A2:A25;G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1; 0))
Result: 25-0103 / 25-0109 / 25-0115 / 25-012125-0103 / 25-0109 / 25-0115 / 25-0121
How the pieces fit together
Watch out: The start must be between 1 and the interval: with a larger start, a pick can fall beyond the end of the list and INDEX returns #REF!.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=ROWS(A2:A25)
=ROWS(A2:A25)
ROWS RIJENThe number of rows in a range.Press Escape to closearrayThe range or array whose rows are counted.Invoice no. column, 24 cellsPress Escape to closeCounts the invoices in the population:
A2:A25has 24 rows, so the population is 24. Because it counts the range, the formula follows when you paste a longer list and stretch the range.=ROUNDDOWN(ROWS(A2:A25)/G1,
0) =ROUNDDOWN(ROWS(A2:A25)/G1;
0) ROWS RIJENThe number of rows in a range.Used here as: numberThe value to cut off toward zero; a number, a cell or a calculation.Press Escape to closearrayThe range or array whose rows are counted.Invoice no. column, 24 cellsPress Escape to close/DividesPress Escape to closeReferenceInput cell: 4Press Escape to closenum_digitsDigits to keep: 0 for whole units, 2 for cents, -2 to cut to hundreds.0: whole numberPress Escape to closeThe sampling interval: population divided by sample size, rounded down to a whole number of rows. 24 / 4 = 6, so you take every 6th invoice. Rounding down (not up) keeps the last pick inside the population.
=SEQUENCE(G1)
=SEQUENCE(G1)
rowsHow many rows the block has.Input cell: 4Press Escape to closeA column that counts the picks: 1, 2, 3, 4. With only the first argument given, SEQUENCE starts at 1 and goes up by 1, as many rows as the sample size in
G1.=G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1,
0) =G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1;
0) ReferenceInput cell: 3Press Escape to close+AddsPress Escape to closerowsHow many rows the block has.Input cell: 4Press Escape to close-SubtractsPress Escape to closeNumberLiteral valuePress Escape to close*MultipliesPress Escape to closeROWS RIJENThe number of rows in a range.Used here as: numberThe value to cut off toward zero; a number, a cell or a calculation.Press Escape to closearrayThe range or array whose rows are counted.Invoice no. column, 24 cellsPress Escape to close/DividesPress Escape to closeReferenceInput cell: 4Press Escape to closenum_digitsDigits to keep: 0 for whole units, 2 for cents, -2 to cut to hundreds.0: whole numberPress Escape to closeThe positions of the selected rows. The fixed start in
G2is 3. The counts 1 to 4 become 0 to 3 after subtracting 1, times the interval 6 gives 0, 6, 12, 18, and adding the start gives 3, 9, 15, 21.=INDEX(A2:A25,
G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1, 0)) =INDEX(A2:A25;
G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1; 0)) INDEXReturns the value at a given row and column of a range.Read more about INDEXPress Escape to closearrayThe block of cells to pick from. Numbering starts at its top-left cell.Invoice no. column, 24 cellsPress Escape to closerow_numWhich row of the block, counted from 1. Use 0 for every row.Input cell: 3Press Escape to close+AddsPress Escape to closerowsHow many rows the block has.Input cell: 4Press Escape to close-SubtractsPress Escape to closeNumberLiteral valuePress Escape to close*MultipliesPress Escape to closeROWS RIJENThe number of rows in a range.Used here as: numberThe value to cut off toward zero; a number, a cell or a calculation.Press Escape to closearrayThe range or array whose rows are counted.Invoice no. column, 24 cellsPress Escape to close/DividesPress Escape to closeReferenceInput cell: 4Press Escape to closenum_digitsDigits to keep: 0 for whole units, 2 for cents, -2 to cut to hundreds.0: whole numberPress Escape to closeINDEX picks the invoice numbers at positions 3, 9, 15 and 21 of
A2:A25and spills them as a column:25-0103,25-0109,25-0115,25-0121.
Step by step
- ROWS(A2:A25) counts the population: 24 invoices, from
25-0101to25-0124. - ROUNDDOWN(24/4, 0) is the interval: 24 / 4 = 6 exactly, so nothing is cut off and you take every 6th invoice.
- SEQUENCE(4) gives 1, 2, 3, 4. Subtracting 1 gives 0, 1, 2, 3.
- Times the interval: 0 × 6 = 0, 1 × 6 = 6, 2 × 6 = 12, 3 × 6 = 18.
- Adding the fixed start 3 gives the positions 3, 9, 15 and 21. All four are at most 24, so every pick is inside the population.
- INDEX(A2:A25, ...) returns the invoice at each position: position 3 is
25-0103, position 9 is25-0109, position 15 is25-0115and position 21 is25-0121. The four numbers spill fromG3down toG6.
Why it works
A systematic sample takes one item per interval, always at the same distance, from a start that is drawn at random once. The formula builds exactly that: the start sets the first position, SEQUENCE counts how many steps to take, and the interval is the step size.
Because the start is a typed number and not RAND, the selection does not change when the sheet recalculates, so you can document it and a reviewer can reproduce it. Change n in G1 to 6 and the interval becomes 4, giving positions 3, 7, 11, 15, 19 and 23.
SEQUENCE and the spill need Excel for Microsoft 365 or Excel 2021 or later.
Watch out for
- A start of 8 here would give positions 8, 14, 20 and 26, and position 26 does not exist, so INDEX returns #REF!. Draw the start with a random number between 1 and the interval, once, and write down how you drew it.
When the population does not divide evenly, ROUNDDOWN leaves a tail that can hardly be selected. With 26 invoices and n = 4 the interval is 6 and the start lies between 1 and 6, so the last pick is at position 24 at most (start 6: 6, 12, 18, 24).
Invoices 25 and 26 can never be picked, and invoices 22 to 24 only with a start of 4 to 6. Check that this is acceptable, or choose an n that fits.
- The sample is only as good as the list. Check the population for completeness first (compare the count and total with the general ledger), and make sure the list is not sorted or grouped in a pattern that repeats at the interval, for example every 6th invoice always being the same customer.
Variations
=INDEX($A$2:$A$25,$G$2+(ROWS($F$3:F3)-1)*ROUNDDOWN(ROWS($A$2:$A$25)/$G$1, 0))
=INDEX($A$2:$A$25;$G$2+(ROWS($F$3:F3)-1)*ROUNDDOWN(ROWS($A$2:$A$25)/$G$1; 0))
The older approach without SEQUENCE: type it in one cell and copy it down four rows. ROWS($F$3:F3) counts 1, 2, 3, 4 as it is copied down. It works in every Excel version and gives the same four invoices.
=INDEX(D2:D25,G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1, 0))
=INDEX(D2:D25;G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1; 0))
The same positions on the amount column D: 2,1752.175, 1,9801.980, 11,27511.275 and 16,48016.480. Together they are 31,91031.910, the value of the selected items.
=INDEX(A2:A25,SEQUENCE(G1, 1, G2, ROUNDDOWN(ROWS(A2:A25)/G1, 0)))
=INDEX(A2:A25;SEQUENCE(G1; 1; G2; ROUNDDOWN(ROWS(A2:A25)/G1; 0)))
A shorter form: SEQUENCE takes the start (third argument) and the step (fourth argument) itself, and gives 3, 9, 15, 21 directly. Same result; the longer main formula shows the arithmetic, which is simpler to explain to a reviewer.
Functions used
- INDEXReturns the cell at a given row and column number inside a range, or a whole row or column of it when one number is 0.
- SEQUENCEBuilds a block of consecutive numbers with a start value and a step, which spills into as many rows and columns as you ask for.
- ROUNDDOWNCuts a number off at a chosen digit position, toward zero, never adding anything: 7.9 becomes 7 and -7.9 becomes -7.
Functions used
- INDEXReturns the cell at a given row and column number inside a range, or a whole row or column of it when one number is 0.
- SEQUENCEBuilds a block of consecutive numbers with a start value and a step, which spills into as many rows and columns as you ask for.
- ROUNDDOWNCuts a number off at a chosen digit position, toward zero, never adding anything: 7.9 becomes 7 and -7.9 becomes -7.