Economists Carmen Reinhart and Ken Rogoff (“R&R”) have been widely cited for a study that concluded that growth rates for countries with public debt over 90% of GDP are lower than for countries with less debt. A recent critique by Thomas Herndon and two other economists examined R&R’s analysis and challenged their conclusions. Their most sensational finding was that R&R’s calculations were skewed in part by a spreadsheet error. R&R acknowledge the error but defend their conclusions.

The spreadsheet error is described here in detail. Instead of averaging the results for all twenty countries in the analysis, R&R included only the first fifteen in the list (which is in reverse alphabetical order for some reason). This has been widely described as “coding” error, as if someone had typed

=Average(F30:F44)

when they meant to type

=Average(F30:F49).

I don’t think this is what happened.

It is more likely that whoever built the spreadsheet began with a smaller list of countries, and created an average formula that included all of those countries. Later on, more countries were added in new rows, and the analyst assumed that the calculation would adjust to include the additional countries. Sometimes Excel updates formulas like this automatically. But not always. This time, Excel did not adjust the formula, making it appear that highly indebted countries had negative average growth when the average was actually positive.

Similar problems have led to spreadsheet disasters in the past. In Good Fences Make Good Spreadsheet, Part 1, I explained how to use “fences”, or bracket rows, to prevent this sort of error.

Another tool of preventative medicine is the Excel table, as I described in Tables vs. Fences. Tables are a good way to organize data and calculations. Once you set up a block of data or calculations, use CTRL+T to make it into a table. Make sure it has header rows and total rows. The total row allows not only for regular totals, but also for averages and other ways to summarize data:

Just click on each cell in the bottom row to select the appropriate summary calculation.

A nice feature of tables is that the summary calculations remain stable even if the data evolves and you need to insert or delete rows.

I find it good practice in Excel to use tables as much as possible. If Reinhart and Rogoff had done so they could have avoided their error.

**Note**: my spreadsheet is for illustrative purposes only, and it may not exactly replicate R&R’s work because of rounding.

Interesting article Win. Excel Tables are indeed very handy.

LikeLike

Thank you Ashwani

LikeLike

Pingback: The Reinhart-Rogoff Film Festival | The Well-Tempered Spreadsheet

re: “I find it good practice in Excel to use tables as much as possible.”

This comment is coming a bit late, but I’d suggest that makes sense, but when analyzing economic data that it is also good practice to avoid Excel as much as possible :-). It is more fully featured than freeware alternatives, but it still has numerical bugs in some of its analysis functions that pop up from time to time that they haven’t fixed over the years. When I first heard about R&R’s error, I was surprised they were using Excel for an analyzing economic data

I have a spreadsheet with many regression calculations that worked fine in OpenOffice and LibreOffice, but broke the most recent Mac version of Excel in Jan 2013 with many cells getting numeric errors (and re-entering the same formula in the same cell leading to different results). There are many articles on the topic around the net questioning its utility for statistics, some of these are about older versions but their pattern of not fixing things doesn’t seem to have changed:

http://www.practicalstats.com/xlsstats/excelstats.html

http://www.csdassn.org/software_reports/gnumeric.pdf

http://www.forecastingprinciples.com/files/McCullough.pdf

http://developers.slashdot.org/story/04/02/23/068219/study-recommends-gnumeric-over-ms-excel

btw, of course I’m sure you realize there are papers other than R&R that look at the debt/GDP vs. growth rate issue. There are also some that indicate higher debt/GDP ratios correlate with higher interest rates, which leads to concern given the short maturity of US debt you have commented on. Although you probably know more about the topic than I do and have seen these already, just in case there are links to some of the interest rate papers can be found if you search for “debt-GDP” on this page:

http://www.politicsdebunked.com/article-list/budget-lottery

LikeLike

Thanks for your comment, and for the interesting links (which lead to even more interesting links).

Agreed, Excel probably wasn’t the best choice for R&R. In addition to Excel’s shortcomings in statistics, it can be a nightmare when you have missing data. In a recent note by R&R, they describe their process for correcting errors in the original analysis. This statement caught my attention:

“Also, in cleaning up the spreadsheets, we took the opportunity to check every cell, and

there were a few blank, but non-zero, entries that could throw off formulae.”

This suggests that R&R were uncomfortable (understandably) with the tool they were using.

LikeLike

re: ” In addition to Excel’s shortcomings in statistics”

Although there are often better tools than spreadsheets to use, with simple tasks (especially when you don’t expect to do hardcore statistics) it is understandable that people fall back on spreadsheets to either do things quickly or so the results can be examined by a wider population familiar with the common tool. I just figure if you are going to use a spreadsheet, since there are open source alternatives that seem to be fixed faster, you may as well play it safe in case your task expands to something requiring numeric stuff that risks breaking Excel. Obviously all software can have bugs so if possible it is best to use two different spreadsheets that don’t share a common code base to confirm something if you aren’t just doing a typical simple business spreadsheet

I was forced to switch to Excel to put one spreadsheet up on that last page i linked to. I should have used tables as you suggest for it. I knew that in theory, but I normally develop regular software and don’t have the right habits ingrained for methods for quick and dirty spreadsheets so I went the easy route. Spreadsheet quality can easily deteriorate if they aren’t well structured, just like regular software, but I hadn’t had time to redo it. (which is why it is better to structure things right to begin with). It is unfortunate that some features like scenarios and name scopes aren’t quite a match between Excel and freeware alternatives so if you want to be compatible it limits the features you can use.

LikeLike

re: “Thanks for your comment, and for the interesting links (which lead to even more interesting links).”

btw, I don’t know if the PoliticsDebunked.com link was one of the ones of interest, I just thought I’d mention that critique is always welcome from knowledgeable sources even though comments aren’t enabled on the site. I haven’t had time to spread the site far and unfortunately some of the info is getting dated. Email is “contact” at that domain (I live in a liberal town so I keep my identity low profile on the net).

LikeLike

Your site is interesting and contains material that is worth checking out regardless of one’s politics. For example, I had been unaware of the Federal Reserve’s study of the quality of the government’s debt projections.

LikeLike

While I agree that tables offer some help in avoiding insertion errors, they require that the user uses them correctly. However, they do not prevent errors from being made as well as a true data tables. Excel tables still allow for text to be combined with numbers, and for formulas to reference cells outside to the table, or worse be overridden. Granted there are other ways to prevent those things from happening, but because there is no built-in governance, and it is just too easy to take short cuts, Excel will always be too prone to errors and should not be taken seriously for critical analyses.

LikeLike

I’m reminded of the commercial: “I don’t always drink beer, but when I do …”

I don’t always use Excel. But when I do, I try to use it well, and tables are a big help.

LikeLike