Bug-hunting spreadsheets

| geek, ibm, work

There’s a certain delight in working on obscure problems. In my case, I was trying to debug an old spreadsheet that statistically analyzed survey responses in order to match them to innovation archetypes. It started when I noticed that a few chart labels were incorrect. In the process of trying to figure that out and make handling multiple survey responses easier, I ended up deep in Visual Basic code, untangling algorithms that didn’t make complete sense to me.

The spreadsheet had been created three years ago, and it was hard to track down people associated with the project. Documentation was practically non-existent. Working with the source code, sprinkled comments, and formula auditing tools, I figured out what was going on.

So I knew what the code did, but I wasn’t sure it did what it was supposed to do. I asked two team members and a third consultant to refer me to someone who could explain the manual procedure and provide the missing information. If I could figure out how to do the analysis by hand, I could find the bugs and update the spreadsheet.

Even if we end up discarding the tool, I had fun following the logic through the code. There’s something about understanding a small piece of the puzzle well, and then expanding your understanding until you can hold the program in your mind.

You can comment with Disqus or you can e-mail me at sacha@sachachua.com.