How to avoid making an Excel mistake like Rogoff and Reinhart

April 16, 2013
April 16, 2013

Whoops.

An apparent error in Carmen Reinhart and Kenneth Rogoff’s influential study of government debt was the result of a simple mistake in Microsoft Excel that all spreadsheet jockeys fear. Here it is:

reinhart_rogoff_coding_error_0

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:

Spreadsheet example - Apple's earnings

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.

Excel-with-double-click

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.)

Trace-Precedents-highlighted

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.

Excel spreadsheet example--precedents1

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.

Excel spreadsheet example--precedent2

Voilà! Never falsely justify global austerity measures again.

Top News

Powered by WordPress.com VIP
Follow

Get every new post delivered to your Inbox.

Join 23,718 other followers