Yesterday I posted about liberating your data from your analytics application. In that post I mentioned that Microsoft Excel is a great data analysis tool to use when exporting information out of your web analytics application. Just now I sat down and read yesterday’s Wall Street Journal and the Talking Tech column by Lee Gomes is all about spreadsheet errors! Talk about timely :)
Ray Panko, a business professor at the University of Hawaii has studied spreadsheet errors for over 12 years. He offered some insight into the error that he sees most.
There are three basic types of errors. One of them is a mechanical error, where you point to the wrong cell when entering a formula or you type in the wrong number. People catch many of those errors. then there are the logic errors-either you don’t have the correct algorithm in your head or you express it incorrectly. But the most dangerous one is omitting something, leaving something out of the spreadsheet. The detection rate for these omissions is close to zero.
Pretty scary, huh? This means that people could be making decisions based on incorrect data. Just imagine that you recommend a marketing manager stop advertising on AdWords because the quality of traffic is poor only to discover there was a bug in the spreadsheet that you used for data analysis. So, what advice does Ray leave us with?
The No. 1 thing is that they’ve got to spend about a third of their spreadsheet development time testing. If they don’t do that, nothing else helps.
That sounds like a software development manager talking! He also recommended locking the spreadsheet so that users can not accidentally type a number in a formula cell. That too sounds like a software development technique. Protect your code from user input, you never know what those crazy users might do.