The dreaded spreadsheet error

By Bill Kaye-Blake 22/04/2013

I posted flippantly last week about the Reinhart and Rogoff (R&R) re-assessment by Herndon, Ash, and Pollin. There’s been more bytes spilled since then. The Economist says it’s not such a big deal, because  ’Ms Reinhart and Mr Rogoff acknowledge in their academic work that this conundrum “has not been fully resolved”, but have sometimes been less careful in media articles.’ Paul Krugman counters that, yes, it is a big deal and provides some links. Matt Nolan at TVHE provides more links and more perspective:

it has been used as an inconsistent marketing tool by people for selling their own unrelated ideological policies….

I’m going to be careful here. Media interviews are not the same as academic writing. Keeping my thoughts straight while listening to someone else’s questions, and then controlling the random thoughts that spring to mind whenever (ask my poor students — I don’t censor digressions quite the same way in lectures) while not babbling — hey, it’s fun and energising but only approximately accurate. So, I’m not going to pile on.

I’m fascinated that it was a spreadsheet error, at least in part. Most economists I know proudly and loudly avoid Excel for anything analytical. Grunty programmes like Stata, sure, and nerdy open-source stuff like R (thanks, Auckland!), absolutely. I mean, these are guys (yes, guys) who sneer at SPSS. To find out that R&R were relying on Excel is like, I don’t know, seeing a celebrity chef eating at Burger King.

There’s a lesson for consultants here. Excel is the sort of programme that gave rise to this:

To err is human, but to really foul things up requires a computer.

Nevertheless, I like Excel a lot. Despite all the stupid and paranoid security controls that Microsoft has added, it is still a portable way to give clients the analytical details of what I’ve done. It also allows me to build dynamic tools to help clients tweak the analysis for their own questions. And, I can show them exactly which number is multiplied by which other number, and then transform it all into pretty pictures clearly and transparently. Throw in some macros and buttons, and it’s really powerful.

The best advice I’ve heard about building those sorts of files is to treat them like programming tasks. You are essentially programming a new bit of software. There are established protocols for tracking versions and checking code — that’s a place to get some tips on good design processes.

It’s the best advice, but I’ve generally ignored it (like a lot of good advice). It’s just too hard. So, let me offer my own advice:

  • do it differently — there are always multiple ways to make calculations. I like to make calculations two different ways, and then check whether they have the same values (‘=A3=B3′ will give a TRUE or FALSE; or, use an IF statement)
  • back-of-the-envelope — just the other day, we were looking at a spreadsheet model (again, portability is important), and we did some back-of-the-envelope calculations to check whether they were sensible. It’s similar to the idea of an elevator pitch — can I explain in simple language and logic why we get these results?
  • have someone check — give it to someone else. Let them see everything, get them to check everything. Make sure they have the chops, too, to do it right. Now, that can be expensive, several hours of work. So ask yourself, do I feel lucky today is it worth it for the job or the client? I mean, if I’m going to recommend unemployment for a few million people, I want to make sure my cell references are right. But not all clients warrant that level of scrutiny.

After all that, though, mistakes will happen. The best thing to do is be a mensch — I’m not sure what the New Zild translation is. Own up, walk the client through the impacts, and do as much work as you need to do with the client to restore some credibility.

And then, add it to your bag of tricks. You’ve just learned an expensive lesson.