Leland Teschler – Executive Editor
On Twitter @ DW—LeeTeschler
I recently heard an application engineer describe a new spreadsheet calculator his Tier Two automotive employer had devised. The spreadsheet helped calculate several technical parameters that went into sizing and selecting a new high-tech automotive component. Meant to be filled out by design engineers at Tier One suppliers, it would be used in the specification of engineered parts valued in the millions, if not tens of millions, of dollars.
I only had one question for the engineer: Who had verified the accuracy and logic of that spreadsheet that was destined to be responsible for so much of the firm’s sales? The answer: “The engineer who wrote it verified it himself….probably.”
For that company’s sake, I hope its spreadsheet is correct. But you’d have to say they have a lot of confidence in a key spreadsheet that no one but the spreadsheet creator has ever examined.
It turns out that this scenario isn’t out of the ordinary, even for researchers with a technical background. So say a couple of computer scientists from the UK. In a paper available from the arXiv.org open-access library maintained by Cornell University, they describe a study of neuroscience researchers who regularly use spreadsheets to tabulate and manipulate data. The majority of the study participants said they didn’t show their spreadsheets to anyone else for a sanity check. Only about 20% did an accuracy check by asking one of their peers to look at their work.
Also interesting was that nearly a third of the researchers in the study didn’t bother documenting their spreadsheets as to the inputs, computations, output, and data point entries. And researchers tend to be self-taught when it comes to spreadsheet skills. None of those in the study had taken a training course in how to create spreadsheets. The majority had picked up what they needed to know on the job while about a quarter of them had some tutoring from their peers.
The irony was that though a minority of the researchers in the study had ever seen the inside of a class room devoted to spreadsheet work, almost three-quarters of them rated their own proficiency in Exel as being in the intermediate range. The scientists running the study point out that this reply smacks of overconfidence. They point to a previous study of spreadsheet use in which nine highly experienced spreadsheet developers all were found to have made at least one error, and 63% of the spreadsheets they created overall had errors. Yet when asked about how confident they were in their work, the median reply was “very confident.”
I’d have to say the results these scientists report has the ring of truth. I also have to admit it’s been a long time since I had anyone check a spreadsheet I made. Ditto for anyone asking me to check a spreadsheet they’d created. I suspect that the experience of most technical professionals mirrors my own.
Getting back to the automotive Tier Two supplier, the engineer with whom I spoke did, in fact, have a way ensuring accuracy in spreadsheets. “I once created a huge spreadsheet for a thermal analysis while I had a 103⁰ fever,” he said. “I knew I was sick so I focused intently on my work. When I went back and checked, I had made errors in only two cells, both of which held complicated equations.”
Oddly, creating a spreadsheet while feverish was not one of the practices the UK scientists mentioned.