If you have not read some of Professor Ray Panko’s work, you are missing out. Some fellow GAMPers told me about him several years ago, while working on the GAMP Good Practice Guide for Computerized Lab Systems (1). His specialty is human errors in spreadsheets, mostly focused on Sarbanes-Oxley and “material errors” But his work is applicable to those of us using spreadsheets in the life sciences. Best of all, some good summaries about spreadsheets are available for free on his page at University of Hawaii. (2).
Open his site, grab a beverage and prepare to be…..depressed. Once you see the ways we humans can mess up numbers and spreadsheet formulas, and our low probability of catching the errors, you will be ready to move to a LIMS/ELN. Anything but a spreadsheet.
Let’s summarize some of those reasons for despair:
- Even after careful development, spreadsheets contain errors in 1% or more of cell formulas
- Based on consultant audits, 20-40% of all spreadsheets contain errors
- Human error is about 5% for logical tasks such as writing programs and can increase to 10% or more. Simple mechanical tasks such as typing have error rates around 0.5%
- Code inspection finds errors in about 5% of program statements after review by the developer
- Error rates for novice vs experienced developers were not significantly different
- Error rates deceased incrementally by about 1/3 as group size increased from 1 to 2 to 4
- Individual inspectors catch half or fewer errors while team inspections catch 80% or fewer of all errors
People are hardwired in such a manner that they will make errors in numbers and formulas from 0.5-5% of entries. Experience does not eliminate it. No amount of training can eliminate these errors. And once made, we are moderately able to detect them—at best. Bottom line: if you are using a spreadsheet, good chance you are releasing inaccurate values about 3 times in 100. This is NOT an error you can eliminate by relying solely on people—as mentioned earlier, it is hard-wired in our brains. Our ability to make quick approximations enables us to survive, but at the expense of numeric accuracy in data transcription. Humans are bad a data transcription, period. Any company using people in its process that also seeks after “Zero Deviations” or “Zero Errors” is chasing a fool’s dream. People who believe that manually recorded data can be freed from errors by a single second person review do not understand what the research is saying. Getting to six sigma requires removing people from the day-to-day manufacturing steps.
In the next article, we will discuss some strategies to minimize human errors and make the picture look a little better.
(1) GAMP Good Practice Guide: A Risk-Based Approach to GxP-Compliant Laboratory Computerized Systems (2nd ed.). ISPE Publications, 2012. 156 pp.
(2) “What We Know About Spreadsheet Errors”, Raymond R. Panko, University of Hawaii, College of Business Administration, Honolulu, HI, 1998.
Originally published by Mark Newton (Principal) on 28 June 2018