Please use this identifier to cite or link to this item: https://hdl.handle.net/11681/4062
Title: Reducing spreadsheet errors
Authors: McKay, S. Kyle
Keywords: Electronic spreadsheets
Ecosystem Management and Restoration Research Program (U.S.)
Publisher: Engineer Research and Development Center (U.S.)
Series/Report no.: Technical Note (Ecosystem Management and Restoration Research Program (U.S.)) ; no. ERDC/TN EMRRP-EBA-03
Abstract: Increasing computational power and memory capacity in desktop computers has resulted in access to numerical techniques previously unavailable to many users. Although computational capabilities have increased, many users are unable to take full advantage of this power due to limited programming expertise. Spreadsheets offer these users a means to capitalize upon computational potential without extensive training, though spreadsheet models can confer risks due to high error rates and lack of quality control. Table 1 presents several strengths and weaknesses associated with using spreadsheets as numerical tools for the masses. Both researchers and practitioners have cautioned users about high rates and consequences of errors in spreadsheets. Computer programming and finance literature have shown that, regardless of complexity, errors occur in approximately 90 percent of spreadsheets (Panko 2005; Powell et al. 2008b) and 1-5 percent of cells (Panko and Sprague 1998; Powell et al. 2009). People typically commit undetected errors in approximately 0.5 percent of simple mechanical tasks (e.g., typing) and error rates increase with complex logical tasks (e.g., programming; Panko 2005). Consequently, if 5 percent of cells contain errors, then complex spreadsheets with thousands of cells are likely to contain mistakes. However, amongst error rate studies there is not a clear definition of what constitutes an error or how overall results are impacted by errors (Powell et al. 2008b). Computational errors are not the only errors possible in spreadsheets, and many errors lie outside the realm of what model developers can control, including: data errors (e.g., uncalibrated instrument), input errors, user errors (e.g., manipulation of spreadsheet structure through data sorting), appropriateness of application to the problem, reliance on conceptual or technical information that may be uncertain or erroneous (e.g., sea level rise predictions), misinterpretation of results, and deliberate errors associated with fraud. U.S. Army Corps of Engineers (USACE) Engineer Circular 1105-2-407 requires that all models used for planning be peer reviewed and certified (USACE 2005). Under this policy, a model is defined as “a representation of a system for a purpose” (USACE 2007). Within USACE, spreadsheets are often used to represent systems and inform decisions ranging from dredging schedules to restoration outcomes to budget allocation. These tools range in complexity from simple data storage to intricate models of complex system properties depending on an array of inputs, conditional arguments, and possibly third party add-ins. Although spreadsheets are ubiquitous, high error rates suggest that thorough checking, testing, and auditing are uncommon. This paper provides guidance for quality assurance and quality control practices and techniques for avoiding or reducing errors by: 1) planning spreadsheet development, 2) avoiding errors in development, 3) finding errors, and 4) self-improvement. This document is not intended to be a hard and fast set of “rules,” but rather to provide guidance for spreadsheet developers. This technical note focuses on the USACE standard spreadsheet program, Microsoft® Excel 2003.
Description: Technical Note
Gov't Doc #: ERDC/TN EMRRP-EBA-03
Rights: Approved for public release; distribution is unlimited
URI: http://hdl.handle.net/11681/4062
Appears in Collections:Technical Note

Files in This Item:
File Description SizeFormat 
TN-EMRRP-EBA-03.pdfERDC/TN EMRRP-EBA-03371.78 kBAdobe PDFThumbnail
View/Open