Excel errors and science papers

THREE years ago Thomas Herndon, a young graduate student from the University of Massachusetts Amherst, made a splash among economists. Given an assignment to replicate the analysis behind a published academic paper, he pored over the data used for an influential study on government debt written by Carmen Reinhart and Kenneth Rogoff, both professors at Harvard. Much to the authors’ embarrassment, Mr Herndon found the most elementary of mistakes: they had accidentally omitted five rows of their spreadsheet when calculating an average. When included, the missing figures weakened the paper’s conclusion substantially.

Unsurprisingly, spreadsheet snafus are not unique to economics. A recent study in the journal Genome Biology looked at papers published between 2005 and 2015, and found spreadsheet-related errors in fully one-fifth of articles on genomics that provided supplementary data alongside their text. Although the papers themselves were not necessarily affected, such bugs can create complications for other scientists trying to replicate or build on previous work.

The syntax of genomics makes it particularly difficult for off-the-shelf software to digest. First, spreadsheets often confuse gene symbols for dates. The authors found that Microsoft Excel would often interpret “SEPT2”, which corresponds to the gene Septin 2, as “September 2nd”. The programme also tended to mistake identification codes like “2310009E13” for numbers in scientific notation—in this particular instance, the code would be read as 2.310009 times 10¹⁹. Such hiccups can be quickly resolved by diligent researchers. But they are easy to overlook, and analysts using more sophisticated software may not know to check for them

The paper’s most worrisome finding is that mistakes seem to be proliferating. The authors find that the number of genomics papers packaged with error-ridden spreadsheets is increasing by 15% a year, far above the 4% annual growth rate in the number of genomics papers published. If we extrapolate current trends indefinitely into the future, then by 2025 every spreadsheet attached to a genetics paper will have an error—unless, of course, there is an error in the spreadsheet we used for this calculation.