There has been a lot of buzz in the media lately about error in spreadsheets. The first article that was pointed out to me, “Shocking Paper Claims that Microsoft Excel Coding Error is Behind the Reinhart-Rogoff Study on Debt” by Mike Konczal at Business Insider is in fact, pretty shocking.
In this article Konczal shares how two economists conducted a 2010 study which resulted in the conclusion that “median growth rates for countries with public debt over 90% of GDP are roughly 1% lower than otherwise; average (mean) growth rates are several percent lower.” These results have been widely used by politicians, media, other economists, and the world to help in finding solutions to the Great Recession. It turns out, after digging further, that there were three serious issues with the economists’ research: Selective Exclusions, Unconventional Weighting, and Coding Error.
According to the article, the spreadsheet entirely excluded five countries from the analysis and averaged cells in incorrect data lines. Herndon-Ash-Pollin said, “This spreadsheet error…is responsible for a -0.3 percentage-point error in RR’s published average real GDP growth in the highest public debt/GDP category.”
Konczal goes on to write, “… all I can hope is that future historians note that one of the core empirical points providing the intellectual foundation for the global move to austerity in the early 2010s was based on someone accidentally not updating a row formula in Excel.”
The second article shared with me was called “88% of Spreadsheets Have Errors” by Jeremy Olshan at Market Watch. In this article Olshan reviews an article by Raymod Panko. Panko explains that close to 90% of spreadsheet documents contain errors and that in most spreadsheets, 1% or more of formula cells contain errors. Multiply that by thousands of data points and formulae and there could be a plethora of undetected errors. Panko says “errors in spreadsheets are pandemic.”
Olshan goes on to reference the 2010 study explored above saying, “By failing to include certain spreadsheet cells in its calculations, the study by Harvard economists… may have overstated the impact that debt burdens have on a nation’s economic growth.” Panko warns in his paper. “Despite this long-standing evidence, most corporations have paid little attention to the prospect of serious spreadsheet errors”.
If you’re an engineer, there are better tools to help you avoid such errors within your organization, specifically engineering calculations software like PTC Mathcad. You should check out PTC Mathcad’s cool, new infographic on the dangers of spreadsheets to learn more on the topic or try PTC Mathcad Express to see how its natural math notation, documentation, and dynamics units can help you eliminate error in your mathematics.
For even more resources, check out this blog post where we highlighted an article Design News wrote about not risking your important calculations to spreadsheets or read these two posts written by John Sheehan, one of PTC Mathcad’s Technical Fellows, in which he highlights the pros of using PTC Mathcad over Spreadsheets.