The dreaded spreadsheet error

22/04/2013 § 2 Comments

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.

About these ads

Tagged: , , , ,

§ 2 Responses to The dreaded spreadsheet error

  • Ivor Davies says:

    All good “best practice” examples of how to minimise spreadsheet risk, but your post seems to indicate there should be an alternative application to use for this kind of modelling. What alternatives are out there that would provide the same flexibility and functionality while ensuring a greater level of accuracy in the calculations?

    • Bill says:

      The problem is that there isn’t an alternative to spreadsheets (Excel, Lotus 1-2-3, Google) for the combination of flexibility, functionality, and presentation. So, we have to learn to use them well.

      Essentially, they trade off accuracy and replicability against presentation and flexibility. If you don’t need their prettiness, and accuracy is more important, then something like R in which you run command code over a data file is preferable. It is self-documenting and a few simple built-in checks (>summary(x) every once in a while) can help you notice errors.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

What’s this?

You are currently reading The dreaded spreadsheet error at Groping towards Bethlehem.

meta

Follow

Get every new post delivered to your Inbox.

Join 112 other followers

%d bloggers like this: