A few weeks ago, Thomas Herndon, Michael Ash, and Robert Pollin published a critique of an influential economic paper by Carmen Reinhart and Kenneth Rogoff on the relationship between a country’s debt-to-GDP ratio and its economic growth. Herndon, Ash, and Pollin uncovered a number of problems in Reinhart and Rogoff’s influential paper,¹ including an incorrect range selection in an Excel spreadsheet formula that excluded several countries from the analysis. Although the spreadsheet error was not the most significant of the errors found, the media coverage has focused predominantly on this issue.² Media coverage like this raises questions about whether the use of a spreadsheet contributed to the errors, and whether a spreadsheet was an appropriate choice for this type of analysis. Regardless of the significance of this particular error, these are important questions because spreadsheets are widely used for all kinds of analysis.

One of the central principles of usability is that the design of a tool has some responsibility for the errors that the users of that tool make when they use it.³ To what extent, then, can we blame errors like the ones that Reinhart and Rogoff made on the spreadsheet as a tool, rather than the investigators themselves or their processes that they used?

One way to answer this question is to look at how similar technologies deal with managing user error. In his 1998 paper (revised in 2008), What We Know About Spreadsheet Errors, Raymond Panko summarizes the research about spreadsheet errors and points out a number of similarities between spreadsheet error rates and error rates in programming disciplines.⁴ It might be useful, then, to look at some of the techniques that programmers use reduce errors, and see whether it is possible to take advantage of them in spreadsheet development. As it turns out, many of these techniques are difficult to implement when working with spreadsheets. Here are a few of these techniques:

Encapsulation

Encapsulation allows people to define a complex calculation, name it, and use it later without worrying further about the implementation. Encapsulation provides useful units for debugging, since encapsulated code should have clear contracts for input and output. Encapsulation also facilitates re-use, since the named entity can be referred to over and over. Copy-and-paste activity is often a signal of encapsulation failure.

This concept is one of the primary mechanisms software developers use to manage complexity in code, and it shows up in a variety of ways, including variable and constant naming, function decomposition, object-oriented design, and layered implementation stacks (such as the network stack), to name just a few.

Spreadsheets have partial encapsulation (cell references), but cells are not named and don’t provide a mechanism for understanding and defining the contracts that a cell calculation should uphold. Cells are often organized into columns where all the cells should have the same semantics, but every cell in such a column will actually have a different definition. Creating accidental errors in such a system is easy, and identifying and fixing errors is difficult. Copy-and-paste of calculations is so common that there are shortcuts built into the application to support it (pasting a calculation from a single cell into a range will copy a modified version of the calculation into every cell in the range, for example).

Sharing and Source Control

Sharing allows multiple people to use a calculation, and also to check the work. This is essentially an extension of the re-use enabled by encapsulation, since the more people that use a single section of code, the more likely it will get attention if it is not working properly. In addition, shared code reduces the proliferation of errors, allowing a fix for an error to be deployed immediately to all the places it is used. In programming, sharing is facilitated by the use of well defined interfaces or APIs, and by the use of source control systems which allow multiple people to collaborate on complex projects.

Spreadsheets do not have any mechanism for sharing and reusing portions of code. Duplicating documents (for communication or extension) is not sharing for reuse by this definition. Excel has some ability to share remote data, but I suspect that this facility is rarely used.

Transparency and Readability

Since errors are a gap between user intention and system behavior, identifying and fixing errors depends heavily on how easy it is to read the code and determine what it does and what it is supposed to do.

Spreadsheets are not particularly readable. Formulas are hidden until you enter edit mode for a cell, so a lot of user interaction may be necessary to see what is going on behind the scenes in a spreadsheet. In addition, cell references are much harder to interpret than named variables, and there is a tendency for components of calculations to be scattered across multiple cells.

Conclusion

Spreadsheets have lots of great attributes that drive their usage for analysis. They are commonly available. They don’t require any special skills or environments to get started. They are flexible and quick to implement. They provide instantaneous feedback when you make changes, giving them a short code-to-result cycle. Despite all of this, they are poor choices for sophisticated analysis, because they don’t facilitate the use of techniques that reduce the error rate for complex implementations.