Errors In Spreadsheets Research Articles

A Comparative Study of Automated Testing Tools for Spreadsheets

Organizations and industries use spreadsheet programs, such as MS Excel, for various tasks, such as accounting, financial calculations, and reporting. However, the number of spreadsheets that contain errors is very high. One primary reason is that different end-users develop spreadsheets without programming or software development training. Thus, researchers have proposed a variety of automated tools and techniques to support the end-users in finding and fixing errors in spreadsheets. The main objective of this paper is to study different automated tools in spreadsheet testing and classify them according to two criteria: how they work and the ability to find spreadsheet faults. For this purpose, we discuss various spreadsheet testing automated tools, techniques, and strategies. In addition, we highlight their performance and where they can be used so that they can be beneficial to both end-users and researchers. We then set out key directions for potential future works.

Investigating the Usefulness of Metric-based Prediction Method for Spreadsheet Fault Detection

The ability to predict whether a specific section of a spreadsheet is faulty or not is frequently required for the development of spreadsheet functionality. Although errors in such spreadsheets are common and can have serious consequences, today’s spreadsheet creation and management tools offer weak capabilities for defect detection, localization, and fixing. In this thesis, we proposed a method for predicting faults in spreadsheet formulas that can detect faults in non-formula cells by combining a catalog of spreadsheet metrics with modern machine learning algorithms. An examination of the individual metrics in the catalog reveals that they are suited to detecting data where a formula is expected to have flaws. In this framework, Recall Score of 99% was achieved and performance was compared with that of Melford. The result of the experiment reveals that the proposed framework outperforms Melford framework.

A mental model perspective for tool development and paradigm shift in spreadsheets

To address the problem of errors in spreadsheets, we have investigated spreadsheet authors׳ mental models in a hope of finding cognition-based principles for spreadsheet visualization and debugging tools. To this end, we have conducted three empirical studies. The first study explored the nature of mental models of spreadsheet authors during explaining and debugging tasks. It was found that several mental models about spreadsheets are activated in spreadsheet authors׳ minds. Particularly, when explaining a spreadsheet, the real-world and domain mental models are prominent, and the spreadsheet model is suppressed; however, when locating and fixing an error, one must constantly switch back and forth between the domain model and the spreadsheet model, which requires frequent use of the mapping between problem domain concepts and their spreadsheet model counterparts. The second study examined the effects of replacing traditional spreadsheet formulas with problem domain narratives in the context of a debugging task. Domain narratives were found to be easy to learn and they helped participants to locate more errors in spreadsheets. Furthermore, domain narratives also increased the use of the domain mental model and appeared to improve the mapping between the domain and spreadsheet models. The third study investigated the effects of allowing spreadsheet authors to fix errors by editing domain narratives, thus relieving them from the use of traditional low-level cell references. This scenario was found to promote spreadsheet authors using even more of their domain mental model in a manner that completely overshadowed the use of their spreadsheet mental model. Thus, from a mental model perspective, it is possible to devise a new spreadsheet paradigm that uses domain narratives in place of traditional spreadsheet formulas, thus automatically presenting spreadsheet content so that it prompts spreadsheet authors to think in a manner that closely corresponds to their mental models of the application domain.

Spreadsheets and the Violence of Forms: Tracking Organisational and Domestic Use

Spreadsheets and the Violence of Forms: Tracking Organisational and Domestic Use

Examining Errors in Simple Spreadsheet Modeling from Different Research Perspectives

By using a sample of 1st-year undergraduate business students, this study dealt with the development of simple (deterministic and non-optimization) spreadsheet models of income statements within an introductory course on business informatics. The study examined students' errors in doing this for business situations of their choice and found three kinds of errors: errors in selecting variables, errors in initializing variables, and errors in relating variables. This article describes these errors, discusses possible reasons for them from different research perspectives, and gives suggestions for further practice and research. These perspectives dealt with modeling with spreadsheets, spreadsheets as mindtools, and errors in operational spreadsheets. The article is primarily directed to teachers of modeling with spreadsheets, interested in taking a broader view of the subject.

Reasoning about spreadsheets with labels and dimensions

Labels in spreadsheets can be exploited for finding formula errors in two principally different ways. First, the spatial relationships between labels and other cells express simple constraints on the cells usage in formulas. Second, labels can be interpreted as units of measurements to provide semantic information about the data being combined in formulas, which results in different kinds of constraints. In this paper we demonstrate how both approaches can be combined into an integrated analysis, which is able to find significantly more errors in spreadsheets than each of the individual approaches. In particular, the integrated system is able to detect errors that cannot be found by either of the individual approaches alone, which shows that the integrated system provides an added value beyond the mere combination of its parts. We also compare the effectiveness of this combined approach with several other conceivable combinations of the involved components and identify a system that seems most effective to find spreadsheet formula errors based on label and unit-of-measurement information.

Errors in Operational Spreadsheets

Spreadsheets are used in almost all businesses, for applications ranging from the mundane to the mission-critical. Errors in the data, formulas, or manipulation of spreadsheets could be costly, even devastating. The received wisdom is that about 5% of all formulas in spreadsheets contain errors, and this rate is consistent across spreadsheets. However, this estimate is based on five studies, some of which are quite informal, and a total of only 43 spreadsheets. Our research was designed to deepen our understanding of spreadsheet errors. Specifically, we address three questions about errors in operational spreadsheets: what is the average cell error rate, how does it differ among spreadsheets, and what types of errors are most prevalent? We created a spreadsheet auditing protocol and applied it to 50 diverse operational spreadsheets. We found errors in 0.9% to 1.8% of all formula cells, depending on how errors are defined. We also found that the error rate differed widely from spreadsheet to spreadsheet.

Automatic detection of dimension errors in spreadsheets

We present a reasoning system for inferring dimension information in spreadsheets. This system can be used to check the consistency of spreadsheet formulas and thus is able to detect errors in spreadsheets. Our approach is based on three static analysis components. First, the spatial structure of the spreadsheet is analyzed to infer a labeling relationship among cells. Second, cells that are used as labels are lexically analyzed and mapped to potential dimensions. Finally, dimension information is propagated through spreadsheet formulas. An important aspect of the rule system defining dimension inference is that it works bi-directionally, that is, not only “downstream” from referenced arguments to the current cell, but also “upstream” in the reverse direction. This flexibility makes the system robust and turns out to be particularly useful in cases when the initial dimension information that can be inferred from headers is incomplete or ambiguous. We have implemented a prototype system as an add-in to Excel. In an evaluation of this implementation we were able to detect dimension errors in almost 50% of the investigated spreadsheets, which shows (i) that the system works reliably in practice and (ii) that dimension information can be well exploited to uncover errors in spreadsheets.

Impact of errors in operational spreadsheets

All users of spreadsheets struggle with the problem of errors. Errors are thought to be prevalent in spreadsheets, and in some instances they have cost organizations millions of dollars. In a previous study of 50 operational spreadsheets we found errors in 0.8% to 1.8% of all formula cells, depending on how errors are defined. In the current study we estimate the quantitative impacts of errors in 25 operational spreadsheets from five different organizations. Within these 25 spreadsheets we identified 381 potential errors, of which 117 (31%) were confirmed as errors by the developers of the spreadsheet. Among these confirmed errors, 47 (40%) had no quantitative impact on the results. Among the remaining 70 confirmed errors, the largest error was $100 million; however, 9 of the 25 spreadsheets tested had no errors at all.

Detecting Spreadsheet Errors: An Education Case

In the past two decades, computing applications developed by non-system professionals have increased dramatically throughout the professional accounting and business community. Unfortunately, prior research indicates that many end-user computing applications such as spreadsheets contain difficult-to-detect errors. Relying on these error-prone applications, professionals may make inappropriate decisions that can negatively impact financial results. This case enhances students' understanding of end-user computing risk by illustrating the difficulty of detecting errors in spreadsheets. Furthermore, this case helps students improve their ability to detect spreadsheet errors.

An auditing protocol for spreadsheet models

Errors are prevalent in spreadsheets and can be extremely difficult to find. A number of audits of existing spreadsheets have been reported, but few details have been given about how the audits were performed. We developed and tested a new spreadsheet auditing protocol designed to find errors in operational spreadsheets. Our work showed which auditing procedures, used in what sequence and combination, were most effective across a wide range of spreadsheets. It also provided useful information on the size and complexity of operational spreadsheets, as well as the frequency with which certain types of errors occur.

The Role of Visualization Tools in Spreadsheet Error Correction from a Cognitive Fit Perspective

Errors in spreadsheets pose a serious problem for organizations and academics. This has resulted in ongoing efforts to devise measures for reducing errors or efficient ways of correcting them. Visualization tools are often advertised as means for improving spreadsheet error correction performance. This study investigates the role of visualization tools in spreadsheet error correction. For this purpose, this study proposes a framework for classifying activities associated with spreadsheet error correction. The framework is to highlight the activities that are important for correcting different types of spreadsheet errors, and to show how different visualization tools can aid error correction by effectively supporting these activities. By identifying “chaining” as one of the most important activities from the framework, this study uses cognitive fit theory to examine the effects of a visualization tool that supports chaining on spreadsheet error correction performance. Experimental methodology is used to test the outcome of cognitive fit between the error correction task and the visualization tool. The results of the experiment highlight the importance of cognitive fit between the type of task and the visualization tool for attaining better performance. This study also provides guidelines for designing and developing tools for spreadsheet error correction.

UCheck: A spreadsheet type checker for end users

Spreadsheets are widely used, and studies have shown that most end-user spreadsheets contain non-trivial errors. Most of the currently available tools that try to mitigate this problem require varying levels of user intervention. This paper presents a system, called UCheck, that detects errors in spreadsheets automatically. UCheck carries out automatic header and unit inference, and reports unit errors to the users. UCheck is based on two static analyses phases that infer header and unit information for all cells in a spreadsheet. We have tested UCheck on a wide variety of spreadsheets and found that it works accurately and reliably. The system was also used in a continuing education course for high school teachers, conducted through Oregon State University, aimed at making the participants aware of the need for quality control in the creation of spreadsheets.

The Effect of Incentives on Knowledge Sharing in Computer-Mediated Communication: An Experimental Investigation

Do financial incentives increase knowledge sharing in a computermediated environment? Thirty-six accounting students assigned to groups with different financial incentives (group, piece-rate, or tournament) searched for errors in accounting-related spreadsheets, with access to an online chat room they could use for helping others. The dependent variable was the amount of knowledge the students shared with others. Quantitative and qualitative data suggest that the group financial incentives inspired more knowledge sharing than did either tournament or piece-rate. Results suggest that managers should carefully consider incentive structures in computer-based systems because incentives potentially affect knowledge sharing.

End-user software engineering

End-user programming has become the most common form of programming in use today [2], but there has been little investigation into the dependability of the programs end users create. This is problematic because the dependability of these programs can be very important; in some cases, errors in end-user programs, such as formula errors in spreadsheets, have cost millions of dollars. (For example, see www.theregister.co.uk/content/67/31298.html or panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm.) We have been investigating ways to address this problem by developing a software engineering paradigm viable for end-user programming, an approach we call end-user software engineering .

Y2K or Bust

Y2K or Bust