Random Sampling: The Audit Sample Was Four Invoices, and It Was a Different Four Every Time the File Was Opened

Fourteen supplier invoices for August, £40,231.00 in total. The spot check was four of them, drawn at random, and the working paper reads: no exceptions found, £11,166.80 of spend tested, 27.76% coverage.

A week later the file was reopened to answer a reviewer's question. The four highlighted rows were a different four. They were worth £7,228.40, and the coverage cell beside them — the one that had said 27.76% — now said 17.97%.

Nothing had errored. Nobody had edited the sheet. The sample lived in a helper column of =RAND(), and RAND draws a new number every time Excel calculates: on open, on edit, on F9, on a filter change three sheets away. Both numbers in that working paper are internally consistent, which is the whole problem. The document describes a test that was never performed, and it describes it correctly.

What this covers. RAND, RANDBETWEEN, RANK.EQ, SMALL, LARGE, INDEX, MATCH, COUNTIF, SUMIF, SUMIFS, SUMPRODUCT, ISFORMULA, MOD and ROUND work in every version this century. RANDARRAY, SORTBY, SEQUENCE, TAKE, FILTER, UNIQUE and LET need Microsoft 365 or Excel 2021, and each section says which it is using. An audit sample is only the example: QA inspection, A/B assignment, a prize draw, a call-quality review and a stocktake spot count are the same construction with a different word for "invoice", and every trap below applies unchanged.

1) Fourteen Invoices, and the Two Numbers a Sample Exists to Produce

The ledger sits in A1:D15 — invoice number in A, supplier in B, date in C, amount in D.

Two cells before anything else, because every check in this article is one of them:

=COUNTA($A$2:$A$15)     → 14           the population size
=SUM($D$2:$D$15)        → 40,231.00    the population value

A sample of four is 28.57% of the rows. It is not 28.57% of the money, and how far off it lands is the subject of section 7: the four smallest invoices are £756.50 between them, 1.88% of the total, and the four largest are £32,755.90, 81.42%. Same sample size, same method, a forty-three-fold difference in what got looked at.

🎯 Scenario: Write the population size and the population value into two labelled cells before you draw anything, and never retype either. Every later check compares against them, and a sample whose population total was typed by hand is a sample of a number, not of a ledger.

2) What "Volatile" Actually Means Here

Three functions produce random numbers, and all three are volatile — they recalculate whenever anything in the workbook does, not only when their own inputs change:

=RAND()                        → 0.7213…    a decimal in [0, 1)
=RANDBETWEEN(2,15)             → 9          a whole number, both ends included
=RANDARRAY(14,1,0,1,FALSE)     → 14 values  spilled down, 365 / 2021 only

Volatile is not a warning label on these three, it is their definition: a function that returned the same number twice would not be random. The trouble is that Excel's idea of "recalculate" is much broader than a user's idea of "change something":

• opening the file;
• typing anything into any cell of any sheet;
• pressing F9, or Enter on an unrelated formula;
• applying, changing or clearing a filter;
• inserting or deleting a row anywhere;
• printing, in some versions;
• opening a second workbook that links to this one.

Each of those redraws every RAND in the file. On a sheet with a sampling column that means the sample identifies different rows than it did a moment ago, silently, with no undo entry and no visual change beyond different rows being highlighted.

The failure in the opening paragraph is worse than "the sample changed", though. The coverage cell was =SUMPRODUCT(--(F2:F15<=4),$D$2:$D$15)/SUM($D$2:$D$15) — computed live from the ranks. So when the draw moved, the coverage figure moved with it and stayed consistent. A stale sample beside a stale total is a discrepancy somebody notices. A fresh sample beside a freshly recalculated total is a working paper that agrees with itself and describes nothing that happened.

🎯 Scenario: Treat any live RAND in a saved workbook as a defect, not a feature. The moment a draw is decided, the random numbers have done their job and should stop being formulas — section 3 is the whole of how.

3) Freezing the Draw

The step everybody skips takes four seconds:

1. Select the helper column of =RAND() — E2:E15 here.
2. Press F9 if you want one last draw. This is the last moment the sample is undecided.
3. Copy (Ctrl+C), then Paste Special ▸ Values (Ctrl+Alt+V, then V) over the same selection.

The column now holds fourteen fixed decimals. Nothing in the file redraws them: not opening it, not editing it, not F9. Everything downstream — ranks, picks, coverage — becomes a stable consequence of fourteen numbers that are now data.

Two supporting habits are worth the trouble on anything that will be reviewed:

• Draw in Manual calculation mode. Formulas ▸ Calculation Options ▸ Manual, build the sheet, press F9 exactly once, paste values, then switch back to Automatic. Between those two clicks the sheet cannot redraw underneath you.
• Keep the frozen randoms. Do not delete the column after pulling the picks out of it. Those fourteen numbers are the evidence that the selection was not chosen by hand, and they are the only way anyone can re-derive your sample from your population.

And one cell proves it was actually done:

=SUMPRODUCT(--ISFORMULA($E$2:$E$15))    → 0     the draw is frozen

Anything above zero means at least one cell in the helper column is still a formula, which means the sample is still moving. ISFORMULA is in Excel 2013 and later; before that, =COUNT($E$2:$E$15) compared against =COUNTA($E$2:$E$15) catches nothing useful, and the honest substitute is showing formulas with Ctrl+` and looking.

🎯 Scenario: Put that ISFORMULA cell next to the sample with the label "draw frozen (must be 0)". It costs one cell and it converts "I'm fairly sure I pasted values" into something a reviewer can read without opening a single cell.

4) The Row-Number Draw, and Why It Collides

The instinct is to pick row numbers directly:

H2:  =RANDBETWEEN(2,15)      drag down four rows
I2:  =INDEX($A$2:$A$15,H2-1)

It works, right up until it hands you the same invoice twice. RANDBETWEEN samples with replacement — each draw is independent and knows nothing about the ones before it. For four draws from fourteen rows:

P(all four distinct) = 14/14 × 13/14 × 12/14 × 11/14 = 24,024 / 38,416 = 62.54%
P(at least one repeat) = 37.46%

Better than one in three, and a repeat does not look like a fault. It looks like a sample of four with one invoice listed twice — which, tested and written up, is a sample of three reported as four, with the coverage figure double-counting whichever invoice was drawn twice. If that happened to be INV-4406, the £12,480.00, its value would be counted twice against a population of £40,231.00: 31.02 percentage points of claimed coverage from an invoice that was looked at once.

RANDARRAY in whole-number mode has exactly the same property, and spilling makes it look more official:

=RANDARRAY(4,1,2,15,TRUE)      four row numbers, still with replacement

The check is one cell, and it belongs beside any row-number draw:

=SUMPRODUCT(--(COUNTIF($H$2:$H$5,$H$2:$H$5)>1))    → 0 if all four are distinct

You can also draw more than you need and de-duplicate — =TAKE(UNIQUE(RANDARRAY(20,1,2,15,TRUE)),4) on 365 — but "draw twenty, hope for four distinct" is a construction that fails on the day it draws nineteen duplicates, and section 5 has a method that cannot collide at all.

🎯 Scenario: Use RANDBETWEEN for things that genuinely repeat — a die roll, a simulated arrival, a test fixture. For picking which rows to look at, sampling with replacement is almost never what you meant, and the 37.46% is the rate at which the difference shows up.

5) The Draw That Cannot Collide: RAND Plus RANK.EQ

Give every row a random number, then take the four smallest. Ranks are distinct by construction, so duplicates are impossible.

E2:  =RAND()                             drag to E15, then freeze it (section 3)
F2:  =RANK.EQ(E2,$E$2:$E$15,1)           drag to F15 — 1 means ascending
G2:  =IF(F2<=4,"SAMPLE","")              the four picked rows

RANK.EQ with a third argument of 1 ranks smallest-first, so ranks 1 to 4 are the sample. RANK without the .EQ still works and is identical here; it is the pre-2010 name kept for compatibility.

Every row has the same chance of landing in the sample — 4/14, 28.57% — which is what "simple random sample" means, and each row's number was drawn independently of every other. Changing the sample size is changing one 4.

The version that pulls the picks into a list of their own:

J2:  =INDEX($A$2:$A$15,MATCH(SMALL($E$2:$E$15,ROW()-1),$E$2:$E$15,0))     drag to J5

SMALL($E$2:$E$15,1) is the smallest frozen random, MATCH finds which row holds it, INDEX returns that invoice number. ROW()-1 gives 1, 2, 3, 4 as it drags; on 365 SEQUENCE(4) is cleaner and spills.

Two things about that formula are worth stating plainly, because both have shipped in real workbooks:

• It must read a frozen column. Written against live RAND() calls instead of a range — MATCH(SMALL(RAND()…),RAND()…) — the two draws are independent and the MATCH returns #N/A, every time, which is at least loud. Written against a live but ranged RAND column it silently re-picks on every recalculation, which is the failure in the opening paragraph.
• Ties are a theoretical problem, not a practical one. Two RAND() values colliding is roughly a one-in-10¹⁵ event. Two rounded values colliding is not: =ROUND(RAND(),2) over fourteen rows collides more often than not, and MATCH would then return the first match twice, quietly making a sample of three. Never round the helper column.

🎯 Scenario: This is the default. It is version-proof back to Excel 2007, it samples without replacement by construction, it survives being emailed, and once the helper column is frozen the whole selection is reproducible from data anyone can see.

6) The Microsoft 365 One-Liner

With dynamic arrays the whole thing is one formula: shuffle the table, take the top four rows.

=TAKE(SORTBY($A$2:$D$15,RANDARRAY(ROWS($A$2:$A$15))),4)

RANDARRAY(14) makes fourteen random keys, SORTBY orders the four columns of the ledger by them, TAKE keeps the first four rows. Before TAKE existed the same result is =INDEX(SORTBY(...),SEQUENCE(4),SEQUENCE(1,4)).

It is elegant and it is more volatile than the column version, not less: RANDARRAY sits inside a live formula, so the spilled block reshuffles on every recalculation and there is no helper column to paste as values. Freezing it means selecting the spilled result and pasting values over it, at which point you have four invoice numbers and no record of the draw that produced them.

Two ways to keep the record:

=SORTBY($A$2:$D$15,RANDARRAY(14))       spill all 14 rows, freeze, take the top 4
=LET(k, RANDARRAY(14), SORTBY(...))     names the keys — but LET redraws too

The first is the honest one: spill the entire shuffled population, paste values over it, and the sample is the top four rows of a permutation anybody can inspect. That is a stronger audit trail than the frozen-column method, because it records the order the other ten rows would have been drawn in — exactly what you need on the day the sample has to be extended from four to six without redrawing.

🎯 Scenario: Use the one-liner when the sheet is yours and the version is known. Use section 5 when the file will be emailed, opened in 2016, or read by somebody who needs to see the mechanism rather than trust it. SORTBY, RANDARRAY, TAKE and SEQUENCE all arrive as #NAME? in older Excel, which turns the sample into an error rather than a wrong answer — the good kind of failure, but still a failure.

7) Random Is Not Representative

Here is the uncomfortable arithmetic, and it has nothing to do with formulas.

Two invoices in this population are over £8,000: INV-4406 at £12,480.00 and INV-4403 at £8,975.00, £21,455.00 between them, 53.33% of all the spend. A correctly drawn simple random sample of four contains neither of them with probability:

C(12,4) / C(14,4) = 495 / 1,001 = 49.45%

Just under half. Not because the draw is broken — because the draw is working. Every row got its 28.57%, the invoice for £96.50 included. The method is faultless and the answer is nearly a coin flip on whether the test looked at the majority of the money.

The population is lopsided, the way purchase ledgers are:

Nine rows out of fourteen — 64% of the population — carry 13.29% of the money. A method that treats those nine as equal in importance to the five is answering "which invoices are typical", when the question asked was "is the spend right".

🎯 Scenario: Before choosing a sampling method, write down the sentence the sample is meant to support. "The process is followed" is a question about rows and wants a flat random draw. "The total is not materially wrong" is a question about money and wants section 8. Using the first to answer the second is the most common sampling error there is, and no formula in this article will catch it.

8) Sampling the Money: Stratified and Value-Weighted

Stratified splits the population into bands and draws separately in each — most simply, test everything over a threshold and sample the rest:

=COUNTIF($D$2:$D$15,">=2000")           → 5      tested in full
=SUMIF($D$2:$D$15,">=2000")             → 34,885.90
=COUNTIF($D$2:$D$15,"<2000")            → 9      sampled, say 3 of them
=SUMIF($D$2:$D$15,"<2000")              → 5,345.10

Five invoices in full plus three of the remaining nine is eight invoices — twice the work of the original four — and it covers 86.71% of the spend outright, before the sampled three add anything. Draw the three with section 5's method applied to the filtered band, not to the whole ledger: rank within the stratum, or the big invoices simply crowd the small ones out again.

Value-weighted — monetary unit sampling — gives each pound an equal chance instead of each row. Build a cumulative column and land a random point in it:

F2:  =SUM($D$2:D2)                                    cumulative, drag to F15 → 40,231.00
H2:  =INDEX($A$2:$A$15,MATCH(RAND()*$F$15,$F$2:$F$15,1)+1)

MATCH with a final argument of 1 finds the last cumulative value not exceeding the random point, and +1 steps to the invoice that point falls inside. The inclusion probabilities stop being flat:

Those last two rows are the whole idea: the £96.50 print invoice, which cannot move the total by anything anyone cares about, drops from a 28.57% chance of consuming a test to a 0.24% chance. Note the trade — value-weighted sampling cannot find an error in an invoice that should exist and doesn't, and it barely looks at small items, so it is a test of overstatement, not of completeness. Two of the three draws are with replacement as written above; de-duplicate with COUNTIF or draw a spare.

🎯 Scenario: On any population where the top 20% of rows carry more than 80% of the value — which is most ledgers, most inventories and most customer books — flat random sampling spends most of its budget on rows that cannot change the answer. Stratify first, then sample within the band that can.

9) Every Third Row Is Not Random

Systematic sampling takes every nth row, which is defensible when the order is arbitrary and indefensible when it is not:

=IF(MOD(ROW()-1,3)=0,"SAMPLE","")      every third row from row 2

Applied here it picks positions 1, 4, 7, 10 and 13 — INV-4401, INV-4404, INV-4407, INV-4410 and INV-4413. Five invoices, £6,758.65, 16.80% of the spend, and not one Kestrel Tooling invoice, the supplier carrying £28,195.90 — 70.09% of everything in the ledger. Kestrel sits at positions 3, 6 and 14; an interval of three starting at 1 lands on none of them.

Start at position 2 instead and you pick 2, 5, 8, 11, 14 — one Kestrel invoice, £8,012.05, 19.92%. Start at 3 and you get positions 3, 6, 9, 12: two Kestrel invoices, £25,460.30, 63.29% of the spend. Same method, same interval, same data, and the answer ranges from 16.80% to 63.29% depending on a choice that usually gets made by whoever typed the first formula on the row their cursor happened to be in.

The fix is not to abandon systematic sampling — it is genuinely useful on a conveyor belt or a call log — but to make the start point the random part:

=RANDBETWEEN(1,3)                      the random start, drawn once and frozen
=IF(MOD(ROW()-1-$K$1,3)=0,"SAMPLE","")

And to check the interval against the data's own rhythm before using it at all. Orbis Cleaning bills exactly £220.00 on a weekly contract; any interval that syncs with a weekly cycle either always catches those or never does, and both outcomes look like a sample.

🎯 Scenario: Sort the population by something unrelated to what you are testing — invoice number, not amount or date — before a systematic pass, and always randomise the start. If you cannot say why the row order is arbitrary, the interval is picking your sample for you.

10) There Is No Seed, So the Record Is the Sample

Every serious statistical tool lets you set a seed, so the same seed reproduces the same draw. Excel's worksheet functions do not: RAND and RANDARRAY take no seed argument, and there is no setting anywhere that makes them repeatable.

Three routes exist, and only one of them is a formula-sheet answer:

• Freeze and keep the numbers. Section 3. The frozen helper column is the seed — anyone can re-derive your exact sample from the population plus that column. This is the route that works everywhere and needs nothing installed.
• The Analysis ToolPak. Data ▸ Data Analysis ▸ Random Number Generation takes a Random Seed box and writes static values into the sheet — no volatility to manage. Its sibling, Data ▸ Data Analysis ▸ Sampling, will draw a random sample directly, but note that it samples with replacement, so section 4's duplicate check still applies.
• VBA. Randomize 4406 followed by Rnd is seeded and reproducible, which is genuinely the right tool when the same draw has to be re-run on a schedule. It also puts the sample inside a macro, where a reviewer cannot see it without opening the editor.

Whichever route, the working paper needs four things written down beside the result, and none of them are automatic: the population definition (which rows were eligible, as a filter anyone can re-apply), the population size and value from section 1, the frozen draw, and the date the draw was made. A sample of four invoice numbers on its own is a list, not evidence.

🎯 Scenario: Save the drawn sample as its own small sheet — population definition, the fourteen frozen randoms, the four picks, the date — and never rebuild the sample in the sheet where the testing gets written up. The one that gets edited is the one that redraws.

11) Four Checks

Put these beside any sample before it leaves your hands. Every one is a single cell, and every one should read zero or a number you already know.

=SUMPRODUCT(--ISFORMULA($E$2:$E$15))                    → 0            the draw is frozen
=SUMPRODUCT(--(COUNTIF($J$2:$J$5,$J$2:$J$5)>1))         → 0            no invoice picked twice
=SUMPRODUCT(--(COUNTIF($A$2:$A$15,$J$2:$J$5)=0))        → 0            every pick is in the population
=SUMPRODUCT(SUMIF($A$2:$A$15,$J$2:$J$5,$D$2:$D$15))     → 11,166.80    value tested, from the picks

The fourth is the one that would have caught the whole story at the top. It computes the tested value from the frozen invoice numbers, not from a live rank column, so it cannot drift with a redraw. Divided by the £40,231.00 from section 1 it gives the coverage — 27.76% — and that figure now means what the working paper says it means.

🎯 Scenario: Label the coverage cell with the sample it belongs to: "coverage — sample drawn 08/09/2026". A percentage with no draw date attached is the one number in a working paper that can silently become a different number.

12) Twelve Traps

1. A live RAND in a saved workbook. The sample changes on open. Freeze it, and prove it with ISFORMULA.
2. A coverage figure computed from a live rank column. It follows the redraw and keeps the document self-consistent while the sample underneath it changes.
3. RANDBETWEEN for row numbers. With replacement: 37.46% chance of a repeat in four draws from fourteen, and a repeat double-counts its invoice in the coverage.
4. ROUND on the helper column. Rounded randoms tie, ties make MATCH return the same row twice, and a sample of four quietly becomes a sample of three.
5. Deleting the helper column after pulling the picks. The sample becomes four invoice numbers with no evidence they were not chosen by hand.
6. Ranking across the whole ledger when sampling a stratum. The big rows crowd out the band you meant to sample; rank within the filtered band instead.
7. Systematic sampling with a fixed start. The start point decides the result — here, between 16.80% and 63.29% of the spend on identical data.
8. An interval that matches a cycle in the data. A weekly £220.00 contract charge and a weekly interval either always coincide or never do.
9. Flat random sampling to test a total. 49.45% of four-invoice samples here miss both invoices over £8,000, which is 53.33% of the money.
10. Value-weighted sampling to test completeness. It cannot find an invoice that should exist and doesn't, because a missing row has no pounds to be selected by.
11. SORTBY, RANDARRAY and TAKE in a file that will be opened in Excel 2016. They arrive as #NAME?, which is loud — but the sample is gone either way.
12. No draw date on the sample. The one piece of context that makes a frozen sample checkable, and the one nobody writes down.

What to Take Away

A random sample is two separate things, and mixing them up is what produces working papers that describe tests nobody ran. The draw is a moment: it happens once, it is random, and the instant it is decided it must stop being random — that is what F9 and Paste Special ▸ Values are for, and =SUMPRODUCT(--ISFORMULA(E2:E15)) → 0 is how you prove it. The method is a choice about what question is being asked: RAND plus RANK.EQ for a flat draw over rows, stratified or value-weighted when the money is lopsided — and here, five invoices out of fourteen carry 86.71% of it.

Get the first wrong and your sample changes overnight. Get the second wrong and it never changes, never errors, and answers a question nobody asked.