Established onExcel 365, Windows and MacExcel 2016 and laterGoogle Sheets, web
An averaging formula covering rows 30 to 44 of a column that runs to row 49 is a valid formula. It computes correctly. It returns an average of something. What it does not do is mention that five rows were left out.
The documented case
A 2010 economics paper reported that countries whose public debt exceeded ninety per cent of national output grew markedly more slowly. The figure was cited heavily in arguments about public spending across several countries.
In 2013 three researchers obtained the underlying spreadsheet and published an examination of it. They found three separate problems, and the one everybody remembers is the smallest.
All three, in order of effect
The weighting. Each country contributed equally to the average regardless of how many years of data it had, so one country with a single bad year counted as much as another with nearly twenty ordinary ones. This accounted for the largest part of the difference.
The exclusions. Certain country-years were left out of the analysis, and the reasons given were not stated in the original paper.
The range. The averaging formula covered fifteen rows where the data ran to twenty, omitting five countries entirely. This was the smallest of the three effects.
Why the smallest one became the story
Because it is legible. A range that stops five rows short is a fact anybody can check and explain in a sentence, and the weighting argument requires understanding why an unweighted average of country averages is a questionable statistic.
The result is that a spreadsheet error is widely credited with overturning an economic argument that it contributed to overturning least. Both halves of that are worth holding: the error was real and uncontested, and it was not the main finding of the examination.
The version that is worse than the wrong range
A range that is short is at least consistent for every row it covers. A range that is inconsistent between rows is not, and it is the commonest form of this defect in ordinary work.
It happens when somebody edits one cell of a calculated column by hand, or copies a block from elsewhere into the middle of one. The column then contains two or three different formulas, all valid, differing in which rows they cover, and the appearance is uniform because a formula is not displayed in its cell.
Both major products can highlight cells whose formula differs from their neighbours, and the entry on inconsistent formulas in the final part covers how. It is the fastest single check in this document and almost nobody runs it.
How a range stops short
Four ordinary ways, none of which produces any indication.
The formula was written when the data was shorter, and rows were added below it afterwards. A formula written as an ordinary reference does not grow when data is appended beneath it.
The range was selected by dragging, and the drag stopped early. On a long column the last visible row is not the last row.
A row was inserted at the very top or the very bottom of the range, which in most spreadsheet software extends a range only when the insertion is strictly inside it.
The formula was copied from somewhere else, where the shape of the data was different.
What prevents it
Referring to a table rather than to a rectangle. Where the data is defined as a table with a name, a formula referring to a column of that table covers whatever the column currently contains, and appending rows extends it automatically. This is available in both major products under different names and is the single most effective structural remedy in this whole part.
Failing that, referring to whole columns, which is safe for aggregate functions and costs recalculation time on large sheets.
And the check that catches it afterwards, which costs one cell: count the rows the formula covers and compare that against a count of the rows the data occupies. Where they differ, something is outside.
What we cannot verify
The original paper, the examination of it and the subsequent exchange between the authors are all published and can be read. The relative sizes of the three effects are taken from the examination's own decomposition, which its authors state and which the original authors disputed in part. We have not reproduced the calculation.
What to look at first in a sheet you did not write
The bottom of every calculated column. Ranges that stop short and rows appended after the fact both show up there, and it takes seconds.
In short
- A range that stops short is a valid formula computing an average of something.
- The documented case had three problems and the range was the smallest of them.
- It became the story because it is legible in one sentence and the others are not.
- Ranges stop short when data is appended, a drag ends early, or a row is inserted at the edge.
- Referring to a named table rather than a rectangle removes the class entirely.
- Comparing the covered row count against the data row count costs one cell.