Apr 23 2013
“For an economist, the five most terrifying words in the English language are: I can’t replicate your results. But for economists Carmen Reinhart and Ken Rogoff of Harvard, there are seven even more terrifying ones: I think you made an Excel error.”
While not a story about manufacturing, it is a cautionary tale that manufacturing professionals who use Excel should ponder.
It is about two economists from a prestigious institution whose sweeping conclusions have been leading foreign governments to adopt disastrous policies and fueled the argument in favor of the same policies in the US.
Reviewing the Harvard paper, researchers Thomas Herndon, Michael Ash and Robert Pollin have discovered that Reinhart and Rogoff had selectively excluded data, calculated averages in “unusual ways,” and made a mistake in an Excel calculation.
On the face of it, the general sloppiness of the work would be forgivable in a summer intern, but the Excel error should give us pause. When inputting the range of a sum, they didn’t drag the cursor down far enough and left five rows out.
With Excel, this kind of error is easy to make and difficult to detect. In spreadsheets generated by others, I have found products with no sales showing positive revenues, and formulas with exponents applied to the wrong parameters. And I suspect others may have found errors in my own.
Following are a few recommendations that may protect you from egg on your face:
- Use meaningful names for cells and ranges. Refer to cells as “GDP” or “Viscosity” rather than “A3” or “RR1.” It will be easier to validate formulas, as they will more closely resemble their mathematical forms and errors will stand out.
- Break down complex formulas into simple ones, with additional cells or columns for intermediate results.
- Include comments explaining your calculations.
- Whenever possible, use built-in functions or pre-existing templates from a trusted source.
- Explain the innards of your spreadsheet to a colleague for validation, and return the favor for his or her spreadsheets.
- Use Excel for calculations and graphic displays, but NOT as a database management system (DBMS). Use a real DBMS for data storage and retrieval.
See on www.theatlantic.com