The cells outlined in dark blue contain the data points that Reinhart and Rogoff used to reach their conclusion that countries with a debt-to-GDP ratio of 90% or higher see average growth of -0.1%. As you can see, they failed to include Denmark, Canada, Belgium, Austria, and Australia. Including those countries—and making a few other adjustments—makes the growth rate 2.2%, according to new research.
The Excel mistake could have been avoided with a few simple tricks.
As an example, here’s a spreadsheet with some figures from Apple’s first quarter earnings this year:
The totals for revenue and expenses are calculated in the spreadsheet by adding up the numbers above those cells, and the value for net income is determined by subtracting total expenses from total revenue. Now, to make sure you typed in the formula for net income correctly, you can double-click on the cell: Not only will Excel reveal the formula, but it will also highlight all of the other cells involved.
That basic technique will save you most of the time. But for complicated spreadsheets—if you have a cell with a formula that involves another cell with a different formula that involves yet another cell, and so on—you might want to trace your math back a few steps. Excel has a feature for that called “trace precedents.” (Below, I’ve highlighted where you can find the “trace precedents” button on Microsoft Excel 2011 for Mac. If you use a PC, you can find instructions here.)
If you highlight the cell with the net income value and then click “trace precedents,” two blue arrows will show you that the value depends on the total revenue and total expenses.
Now, if you click the “trace precedents” button again, Excel will show you all of the cells that total revenue and total expenses depend on—in this case, all of the cells that ultimately affect the net income.
Voilà! Never falsely justify global austerity measures again.