It’s 2010, and the world is coming out of recession. Two Harvard professors–one of whom is a former economist for the IMF and chess Grandmaster–publish a paper suggesting that a country with a high public debt-to-GDP ratio of over 90% is associated with low economic growth. Turns out the Excel model the professors use is riddled with some basic statistical and formula errors. The results potentially lead to incorrect economic policies, austerity measures, and high unemployment around the world. This is a Google Sheet which shows one of the spreadsheet errors, and I show how you can prevent such an error in this post.
See the video below if you want to jump straight to the tutorial:
[…] median growth rates for countries with public debt over 90 percent of GDP are roughly one percent lower than otherwise; average (mean) growth rates are several percent lower.
In 2013, PhD students Thomas Herndon, Michael Ash, and Robert Pollin of the University of Massachusetts, Amherst had re-created the study from Reinhart and Rogoff’s paper as part of their PhD program. The students had to analyze the original Excel files that Reinhart and Rogoff used, and they weren’t able to replicate the original results. They cited in their own paper entitled Does High Public Debt Consistently Stifle Economic Growth? A Critique of Reinhart and Rogoff :
[…] coding errors, selective exclusion of available data, and unconventional weighting of summary statistics lead to serious errors that inaccurately represent the relationship between public debt and GDP growth among 20 advanced economies in the post-war period.
Reinhart and Rogoff suggested that the debt/GDP ratio and economic growth is simply a correlation, and that correlation still holds after correcting for the spreadsheet mistakes. However, that correlation is not as strong as their original paper posited.
Why this was a big deal
The implications of their findings resulted in news outlets, politicians, and policymakers using the 90% benchmark as a signal that a country is heading for low economic growth. Some notable examples:
- 2012 Republican nominee for the US vice presidency Paul Ryan included the paper in hi proposed 2013 budget
- The Washington Post editorial board takes it as an economic consensus view, stating that “debt-to-GDP could keep rising — and stick dangerously near the 90 percent mark that economists regard as a threat to sustainable economic growth.”
- Austerity measures are put into place around the world despite the advice from economic advisers, pushing unemployment rate above 10% in the eurozone
3 main Excel spreadsheet problems with the model
The three main errors that Herndon, Ash, and Polling discovered are the following:
- Years of high debt and average growth where selectively excluded from the data set
- Countries’ GDP growth rates were not properly weighted
- Summary table excludes high-debt and average-growth countries
This video illustrates the three individual problems with the spreadsheet really clearly:
If you fix these errors, the average real GDP growth rate for countries carrying a public debt-to-GDP ratio of over 90% is actually 2.2%, not -0.1%. In the Google Sheet I shared, you wont’ see the correct 2.2% average growth rate since I’m not doing the full analysis and focusing on the third Excel error stated above.
Fixing incorrect cell references for average GDP growth rates
The third error of incorrectly excluding high-growth countries from the average GDP growth rate is a particularly egregious mistake, and Reinhart and Rogoff admit that they made this simple cell referencing mistake. As you can see in the screenshot below, they simply omit rows 45 to 49 in their
Here are three methods Reinhart and Rogoff could have used to ensure that they referenced the correct cells to avoid this mistake:
Method 1: Check the summary dropdown in the bottom-right
After you select all the cells that contain GDP growth rates in column G, you can look at the dropdown in the bottom right of Excel or Google Sheets to see the average. No formulas required:
You can also get other summary stats like the
MAX of your selected range of cells. Probably the easiest method to get a quick sanity check of your averages that you’ve calculated in lines 26-27 of the Google Sheet.
Method 2: Adding a checksum/checkaverage formula to compare results
This one is my preferred method, and is quite common in financial models. Usually you’ll see this type of “error checking” when you want to make sure you’ve captured the correct cell references for a
SUM formula, but with some extra work you can check for averages too.
You start by writing a formula below your actual summary stats (in this case starting on line 28 of the Google Sheet) and create a
SUM formula of the data:
The big question is this: how do you know if you’ve referenced the correct cells in your “checksum” formula? The hope here is that by writing the
SUM formula for the second time, in theory, you won’t make the same mistake twice. Obviously this is a big assumption in this method, but let’s assume you’ve properly made the reference for this internal error-checking formula.
The next formula below the “checksum” is a “count” formula:
Notice how it’s not a
COUNT formula. This is because the table contains the “n.a.” text so a
COUNTA formula would be incorrect since it would count all values in the column. We only want the numeric values, hence the reason for using
Finally, the “checkaverage” formula compares your actual average in line 26 with the result of
count. If the values aren’t equal, then you’ll get the text “Error” as the result of the
Since line 26 references the “incorrect” averages used in Reinhart and Rogoff’s paper, we get errors across the board. This “checksum” or “checkaverage” methodology gives you a visual indicator on whether your calculated results are properly referencing all the cells in the range instead of a subset. Instead of writing a “checksum” and “count” formula, you could simplify the “checkaverage” formula to this:
We simply put the
COUNT formulas inside the first argument of the
Method 3: Create a PivotTable and compare results
This method also relies on you selecting the proper cells to build your PivotTable. Again, assuming you don’t make the same mistake twice, selecting the cells in the range should be a pretty simple task. After you select the cells (B4:G24 in this case), you build a PivotTable with
Country in the Rows and the four debt/GDP buckets in the values. You then summarize each metric with the
The “Grand Total” on the last line of the PivotTable contains the average across all growth rates. You can then compare these numbers to your computed numbers on the first sheet that contains your table.
Lessons to be learned for your own models
People don’t check their analyses with the the above 3 methods because it takes extra work and…well…people are lazy. In addition to putting in error checks to ensure you are not making simple spreadsheet errors like this, there are other strategies you can use to ensure others can replicate your work to detect potential errors.
For Reinhart and Rogoff, they didn’t make their full underlying data public. They only shared their spreadsheet after Herndon, Ash and Pollin reached out to them as the trio was trying to replicate their results. Some other strategies:
- Upload your results to a public repository like GitHub early on in your analysis and “open source” your data
- Write detailed steps on experimental design, procedures, equipment, data processing, and statistical methods used so others can replicate your experiment
I really liked this quote from a commenter about the Excel error on this Stat Modeling blog:
I’d like to see how many researchers expose themselves to such criticism. Uploading a raw dataset is one thing but allowing people to see all your intermediate calculations in messy detail is rare.
Too often we’re caught up in doing all the number crunching ourselves and then sharing the output once we think we’ve crossed finished the analysis. As this example suggests, sharing your data set and model as you are doing the analysis can prevent a blunder like this from happening.
Auto date formatting and human gene naming problems
In the second half of this episode, I discuss an article in The Verge about how the HUGO Gene Nomenclature Committee had to rename gene names because of Excel’s simple feature of auto-formatting dates. Gene names like “MARCH1” and “SEPT1” get re-formatted to the dates “1-Mar” and “1-Sep” when these values are entered into Excel. I thought this was interesting to see the scientific community bending to this standard feature in Excel given the widespread use of Excel in the scientific community.
Other Podcasts & Blog Posts
In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:
- The Verge: Scientists rename human genes to stop Microsoft Excel from misreading them as dates
- This Week In Startups #948: HackerOne CEO Mårten Mickos shares insights on how he grew his bug bounty army to 400,000 strong by providing a path to hack for good, most common security vulnerabilities, worst security breaches, hacking the Pentagon, protecting the open source that unites us & scaling a company culture that defaults to disclosure