Established onAny spreadsheet, any version
A model that divides by the sum of two rates where it should divide by their average produces a number roughly half the size. The expression is arithmetically valid, the cells contain numbers, and nothing anywhere objects.
The documented case
A large bank published its own investigation into losses in a derivatives portfolio in 2012. The report describes, among a great deal else, how a new model for measuring the portfolio's risk was operated: as a series of spreadsheets, with data copied and pasted from one to the next by hand.
The report states that one step of that model divided by a sum where it should have divided by an average, and that this understated the measured volatility of the portfolio. It also describes the manual copying between sheets as a source of operational risk in its own right.
What to take from it and what not to
The loss was caused by the positions, not by the spreadsheet. A model that reported a larger number would not have changed the trades that had already been made; it would have changed how visible the exposure was to the people responsible for limiting it.
That distinction matters because this case is routinely cited as a several-billion spreadsheet error, which conflates the size of the loss with the contribution of the defect. The entry on how failure costs are calculated in the final part of this site is about exactly that conflation.
What the case does demonstrate, from a primary source, is that a significant risk measurement at a major institution ran through spreadsheets with manual transfers between them, and that a single arithmetic substitution in one of them went unnoticed through review.
Where the risk concentrates
Models of this kind accumulate steps, and each step is somebody's cell. The published account of the case above describes a chain in which a figure was produced in one workbook, copied by hand into another, used there, and copied on again.
Manual transfer between files removes every property that made the first calculation checkable. The destination has no link back, no recalculation, and no indication of when the value arrived or from which version of the source. A number that was live becomes a constant, and a constant that looks like a result is the hardest thing in a sheet to audit.
Which is the argument for the arrangement in the fourth part of this site: if a figure must cross a boundary, it should cross as a link that can be traced or as an import that can be repeated, and never as a paste.
Why this class is invisible
Because both expressions are reasonable-looking and their results are in the same units and roughly the same order of magnitude. A total that should be a hundred and is fifty is not obviously wrong to somebody who does not already know what it should be.
Compare it with a division by zero, which produces a visible error value and stops. The arithmetic mistakes a sheet catches are the ones that have no valid answer; the ones it cannot catch are the ones that have a valid answer to a different question.
The family this belongs to
Summing a column that already contains a subtotal, so the total counts part of itself. Averaging a column of averages, which is not the average of the underlying values unless the groups are equal in size. Adding a percentage to a percentage. Multiplying by a rate that was already applied upstream.
All four produce plausible numbers, and all four are visible only by comparison against something computed a different way.
What catches it
Computing the same figure twice by different routes and requiring the two to agree, which is the subject of the entry on cross-footing in the final part. It is the only technique in this document that catches arithmetic that is valid and wrong, because it does not rely on anybody noticing anything.
The cheaper habit that catches a good share: state the units and the expected magnitude in a cell beside the result, before computing it. A number that should be a rate between zero and one, sitting next to a note saying so, is checkable at a glance for ever afterwards.
What we cannot verify
The bank's report is public and describes the model, the manual transfers and the substitution; that is a primary source and an interested one, since it was produced by the organisation concerned. The attribution of any portion of the loss to the model rather than to the positions is not established by it and is not attempted here.
The question to ask of any model
Which figure in this would be most useful to somebody who wanted the result to look better, and how would anyone know if it had moved. Where the answer to the second half is nobody, that is the cell to compute twice.
In short
- Dividing by a sum instead of an average halves the result and raises no error.
- The documented case is described in the institution's own published report.
- The loss came from the positions; the model changed how visible the exposure was.
- A sheet catches arithmetic with no valid answer, not arithmetic answering another question.
- The family includes double-counted subtotals and averages of averages.
- Computing the figure twice by different routes is the only reliable catch.