Why a Spreadsheet Isn't Evidence

Most housing lotteries in this country are run in Excel. I want to be clear at the outset that this is not a scandal, and the people doing it are not being careless.

A spreadsheet is a genuinely good tool for the job. It holds the applicant list you already maintain, it sorts, it exports, everyone on staff knows how to use it, and it costs nothing beyond software the agency already owns. For running a drawing, it works.

The problem is not that spreadsheets run bad lotteries. The problem is that a spreadsheet is structurally incapable of proving it ran a good one, and that limitation only surfaces at the exact moment you need it not to.

The gap, stated plainly

A spreadsheet cannot demonstrate its own integrity.

Open a results file. Change a number. Save it. Now look at the file and tell me whether it was changed.

You can't. Neither can an auditor, a board member, or opposing counsel. The file looks exactly the same either way. There is no property of the document that distinguishes "these are the results the drawing produced" from "these are the results as they stand today."

Everything else in this post follows from that one fact.

Note what I am not saying. I am not suggesting agency staff alter results. In seven years of doing this work I have never seen it, and I would be surprised to. The point is that your inability to prove it didn't happen is independent of whether it happened. An honest agency and a dishonest one produce documents that are equally unverifiable, which means the honest agency gets no benefit from its honesty at precisely the moment the benefit would matter.

Three specific failures

1. RAND() is not built for this

Spreadsheet random functions are pseudo-random number generators optimized for speed and statistical smoothness in ordinary use. They are not designed to be unpredictable to someone who is trying to predict them, and that is a different engineering goal than the one a lottery needs.

They also volatilize: RAND() and RANDBETWEEN() recalculate on every edit, sort, filter, or file open. The practical consequence is familiar to anyone who has done this: you assign random numbers, sort by them, and watch the values change underneath you as the sort executes. The standard workaround is to paste-special the values to freeze them, which works, and which also means the numbers now sitting in your file have no verifiable relationship to any particular moment.

For most spreadsheet work this is irrelevant. For a process that allocates housing and may be challenged, "we generated numbers, they changed while we worked, we froze some of them" is not a description you want to give under oath.

Cryptographically secure random number generation (CSPRNG) exists specifically for cases where unpredictability is a requirement rather than a nicety. Housing allocation is one of those cases.

2. Sorting by a random column introduces bias

The common method (add a column of random values, sort by it) is not a uniform shuffle.

The reason is subtle enough to be worth spelling out. Sorting algorithms have to decide what happens when two values tie, and the standard behavior preserves the original relative order of tied rows. With floating-point randoms, exact ties are rare but not impossible, particularly in large lists. More significantly, spreadsheet sorts are not guaranteed to be uniformly distributed permutations of the input. That guarantee is a property of algorithms specifically designed to shuffle, like Fisher–Yates, and it is not something a general-purpose sort promises.

The effect is usually small. It is also invisible, unverifiable, and impossible to explain in a hearing. "We're fairly confident the sort didn't introduce bias" is a substantially weaker statement than "the shuffle algorithm is provably uniform."

3. There is no record of what happened

Ask a spreadsheet what time entry #47 was drawn. It has no answer, because nothing was drawn. A column was populated and a sort was applied, all effectively instantaneous.

A spreadsheet-run lottery produces a result. It does not produce a process record. There is no log of what occurred, no timestamps on individual selections, no evidence the operation happened at the announced time rather than a week earlier or a month later.

That absence is not a gap you can fill in afterward. Reconstructed documentation is a summary of what someone remembers, and everyone evaluating it, auditors especially, knows the difference.

"But we have version history"

This comes up often enough to address directly. Excel autosave, SharePoint versioning, Google Sheets revision history, and OneDrive file versions all track changes to a document. Doesn't that solve it?

Partially, and not the part that matters.

The deeper issue is one of custody. Version history is maintained by the same system, under the same administrative control, as the file itself. An administrator can typically disable it, purge it, or move a file to a location where it does not apply. That is not a claim that anyone would. It is the reason the record does not resolve the question. A record controlled by the party it is meant to hold accountable cannot settle a dispute about that party's conduct.

This is the same reasoning behind independent audits, notaries, and third-party escrow. The independence is the mechanism.

What actually closes the gap

Three properties, none of which a general-purpose spreadsheet can provide.

Cryptographic signatures derived from the data. A value computed from the contents of the record such that changing any character anywhere produces a different value. Compare the signature to what is on file and you know immediately whether the data is unmodified. This is the mechanism that makes a record tamper-evident: not preventing alteration, but making it detectable, which is what evidence requires.

A contemporaneous processing log. Timestamped entries written as the drawing runs, recording each draw as it happens. Not a summary produced afterward, but a record generated by the process itself while it executes.

Custody outside the interested party. A record produced by a system the agency does not control, so that "you could have changed it" stops being an available objection. This is the property that no internal process can supply, no matter how rigorous, because the objection is about control rather than conduct.

What to do if you're running lotteries in a spreadsheet today

You can improve your position considerably without changing anything about your tooling. In rough order of value per effort:

1. Freeze and archive the input file at the moment the drawing runs. Save it separately, read-only, never edited again.
2. Export the complete draw order, not just the selections. Every position, including the ones past your unit count.
3. Keep a contemporaneous log. Even a simple written record (who ran it, when it started and finished, who observed) is materially better than nothing, and it is evidence in a way that memory is not.
4. Produce the public-safe version immediately, at the same time as the full one, rather than redacting later under deadline pressure.
5. Have someone outside the program observe the run and sign a dated note. Imperfect, and far better than nothing.
6. Write down your method and make sure it matches what your admission policy says. 24 CFR § 982.202(d) requires that policy state the system you use. A mismatch between written policy and actual practice is its own finding, independent of anything else.

These steps address the documentation gap. They do not address the verification gap. The question of how anyone knows a file wasn't changed cannot be solved by a tool that permits editing. But most agencies are further from a complete record than they realize, and the six items above close most of that distance for the cost of an afternoon.

The honest summary

Spreadsheets are fine at running housing lotteries and incapable of proving they ran one. Both halves of that sentence are true, and the second half only costs you anything on the day someone asks a question you cannot answer with a document.

For many agencies, that day never comes. For the ones where it does, the difference between having a verifiable record and not having one is the difference between a short conversation and a long one. And it does happen: an applicant with counsel, an auditor with a finding, a board member with a concern, a reporter with a records request.

The record costs almost nothing to create at the moment of the drawing. It cannot be created afterward at any price. That asymmetry is the entire argument.

Lenora Castrellon is the founder of AuditDraw, which processes housing lotteries as a neutral third party and returns a signed, verifiable record of each drawing. AuditDraw has been running real housing lotteries for agencies since 2019.

Nothing here is legal advice. Program rules vary by jurisdiction and funding source; your counsel and your funder's requirements govern.