i-nth logo

Authors

Atipol Asavametha

Abstract

Spreadsheets are a widely used end-user programming tool. Field audits have found that 80-90% of spreadsheets created by end users contain textual and formula errors in spreadsheets. Such errors may have severe negative consequences for users in terms of productivity, credibility, or profits.

To solve the problem of spreadsheet errors, researchers have presented manual and automatic error detection. Manual error detection is both tedious and time-consuming, while automatic error detection is limited to only finding some formula error categories such as formula reference errors. Both approaches do not provide the optimum result in error detection.

We have tested a new error detection approach by detecting bad smells in spreadsheets, which is an indication that an error might be present. Originally developed for object-oriented programming, examples include the large class, and the lazy class. We have adapted the concept of bad smells to spreadsheets. Each bad smell detector might indicate an issue in the spreadsheet, but the indication is not definitive, since the user must examine the spreadsheet and make a final judgment about whether an error is actually present.

We evaluated 11 bad smell detectors by analyzing the true positives against the false positives. The result shows that six detectors can highlight some error categories, such as categorical errors and typographical errors.

Sample

Process for detecting bad smells in spreadsheets
Process for detecting bad smells in spreadsheets

This is the process for detecting bad smells in spreadsheets. Our bad smell detectors do not detect errors. Instead, they compute a number indicating how bad a particular rectangular region looks to determine whether the user should be invited to manually check that region for errors.

Publication

2012, Master's thesis, Oregon State University, June

Full article

Detecting bad smells in spreadsheets