What a formula hides

Somebody leaves, and you get their workbook.

It opens on a summary tab, which is the only tab anyone had ever looked at. A figure sits in the corner. You click it, and the formula bar says it is a reference to another sheet. That cell is a sum of a range on a third sheet. Somewhere in that range, four rows down, is a number that is not a formula at all. Somebody typed it. There is no note, no date, and no way to tell whether it was typed last quarter or six years ago by a person whose name is not in the file.

I have done that excavation at a software company carrying reporting through a run of acquisitions, and later across an estate of more than a hundred workbooks being lifted onto a new platform. It is the same excavation every time and it always takes longer than the estimate.

The design decision underneath it

The grid’s central move is that it shows you results and hides the reasoning, and it hides the reasoning one cell at a time. You can only ever see the logic of the cell your cursor is in. Everything else on the screen is presenting itself as a fact.

That is not a flaw somebody neglected to fix. It is the entire reason the thing works. A person who has never been taught anything about how the model is built can open it, read the summary, and use the number. The interface is optimised for the reader who does not want to know, and there are far more of those than there are of me.

The cost lands on the auditor, and everyone becomes the auditor eventually. To verify a number you have to walk the chain by hand, cell by cell, and the artefact gives you no map of the chain. It gives you one link at a time and a lot of confident formatting.

So the property that makes it usable and the property that makes it unauditable are the same property. That is why the problem has never been solved. It is not neglect. It is a trade that nobody has found a way to escape.

Every fix I tried taxed the thing that made it work

I went through the standard remedies. Colour conventions for inputs against calculations. A documentation tab. Named ranges instead of coordinates. A rule that no constant is typed into a formula.

They hold for a while and then they decay, and I used to read the decay as sloppiness. I do not think that any more. Each of those layers charges the user for something the artefact promised was free. The promise is that you can change a number and see the answer move. Every discipline I added made that slightly slower, and the person under deadline pressure at ten at night correctly identified my convention as the optional part.

The alternative direction, moving the logic somewhere it can be read properly, solves the audit problem completely and costs you the reader who does not want to know. That reader is usually the person who needs the number to make a decision this week. Trading them away is a real loss and it is rarely counted as one when the migration is being proposed.

I start by finding where the formulas stop

I no longer start by checking whether a workbook is right. I start by finding where the formulas stop.

The boundary between what is calculated and what was typed in is the only honest map of what a model actually knows. Everything downstream of a hardcoded cell is not a calculation, it is an elaboration of somebody’s assumption, presented at four decimal places.

The odd part is how often that typed number turns out to be defensible. There is usually a reason. It is just that the reason and the person left together, and the number stayed behind and kept being multiplied.