We’ve discussed this on the yahoogroup of the European Spreadsheet Risk Interest Group, of which I’m the current chair. Here’s my summary of The Spreadsheet Error Heard Round the World, with my commentary followed by the background.
1) The authors did not catch the error, and only when they released the spreadsheet did others find it.
Lesson: review by a different pair of eyes can catch errors before they cause loss or embarrassment.
“Peer review” is the gold standard in academic research, and the best self-protection for business users of spreadsheets.
Here’s a story from Edward Krudy of Reuters quoted in the Toronto Star: the person who checked the R&R study also checked their own work:
Student finds glaring spreadsheet errors in study used to justify budget slashing
“I almost didn’t believe my eyes when I saw just the basic spreadsheet error,” said Herndon, 28. “I was like, am I just looking at this wrong? There has to be some other explanation. So I asked my girlfriend, ‘Am I seeing this wrong?’” His girlfriend, Kyla Walters, replied: “I don’t think so, Thomas.”
2) Like most spreadsheet creators, they possibly did not realise at the start how important it would become; in their case, frequently cited in support of austerity and arguably the cause of more grief to the world at large than other problems that have merely cost millions. (See http://www.eusprig.org/horror-stories.htm )
Lesson: The more important something is, the more care you have to take with it.
3) Technically, the error could have been spotted by simple tests, such as pressing Ctrl+[ on a formula to show what cells feed into the total.
There are many software tools, such as XLTEST, to point out structural flaws.
There is much guidance on safer spreadsheet construction, books such as “Spreadsheet Check and Control”, “Spreadsheet Safe”, etc.
In the business world, common checks include cross-total balances and reconciliations.
Eusprig papers are published at
http://www.eusprig.org/conference-abstracts.htm
Our Annual Conference will be in Greenwich on 4-5 July 2013.
http://www.eusprig.org/annual-conference.htm
This is a recent blogfire about the unravelling of the Rogoff & Reinhart paper that was influential in austerity policies.
In 2010, economists Carmen Reinhart and Kenneth Rogoff released a paper,
“Growth in a Time of Debt.”
Countries with debt-to-GDP ratios above 90 percent have a slightly
negative average growth rate, in fact.
In a new paper, “Does High Public Debt Consistently Stifle Economic
Growth? A Critique of Reinhart and Rogoff,” Thomas Herndon, Michael Ash,
and Robert Pollin of the University of Massachusetts, Amherst
successfully replicate the results. After trying to replicate the
Reinhart-Rogoff results and failing, they reached out to Reinhart and
Rogoff and they were willing to share their data spreadsheet. This
allowed Herndon et al. to see how how Reinhart and Rogoff’s data was
constructed.
They find that three main issues stand out. First, Reinhart and Rogoff
selectively exclude years of high debt and average growth. Second, they
use a debatable method to weight the countries. Third, there also
appears to be a coding error that excludes high-debt and average-growth
countries. All three bias in favor of their result, and without them you don’t get their controversial result.
At one point they set cell L51 equal to AVERAGE(L30:L44) when the
correct procedure was AVERAGE(L30:L49). By typing wrong, they
accidentally left Denmark, Canada, Belgium, Austria, and Australia out
of the average. When you fix the Excel error, a -0.1 percent growth rate turns into 0.2 percent growth.
Read more:
http://www.latimes.com/business/money/la-fi-mo-debt-excel-error-20130416,0,4073638.story
The reported spreadsheet formula error explains 0.3% of a 2.3% error,
the rest is an argument about selective data, methodology, causation,
correlation, etc. From the look of that table, I doubt if the problem was one of rows added at the bottom. Even if it was, the technical solution from MS is the option “Extend data formats and formulas” which autocorrects formulas at the bottom of columns. Others have checked other averages and found discrepancies which may give rise to suspicion that this was not an unintentional error.
That Reinhart and Rogoff Committed a Spreadsheet Error Completely Misses the Point
http://www.guardian.co.uk/politics/2013/apr/18/uncovered-error-george-osborne-austerity
http://www.guardian.co.uk/commentisfree/2013/apr/18/rogoff-reinhart-deficit-research-false
http://www.guardian.co.uk/commentisfree/2013/apr/16/unemployment-reinhart-rogoff-arithmetic-cause
After being unable to reproduce R&R’s results with publicly available data, HAP were able to get the spreadsheets (zip) that R&R had used for their calculations.
http://www.peri.umass.edu/fileadmin/pdf/working_papers/working_papers_301-350/HAP-RR-GITD-code.zip
(POB: the analysis spreadsheet, crucially, is not in that zip)
http://www.guardian.co.uk/politics/blog/2013/apr/18/comedy-errors-serious-story-missed
