GoalDebug: Goal-Directed Debugging of Spreadsheets

GoalDebug: Goal-Directed Debugging of Spreadsheets

Errors in spreadsheets

Spreadsheets that are used in the real world are full of errors and cause companies and organizations to lose millions of dollars every year. Finding and correcting spreadsheet errors is a very difficult and time-consuming activity.

Debugging is difficult and time consuming

Typically, when programmers observe that their programs do not work correctly, they have to step through it, inspecting many program parts, variables, and values before they can spot the source of the error. When the location of an error is found, the next step is to fix the code, which is not always obvious and can even lead to the introduction of new errors. Debugging is a very expensive undertaking. It is estimated that software engineers spend 70-80% of their time testing and debugging programs, and even for these professionals, finding and fixing errors takes an average of 17 hours.

Making spreadsheet debugging easy

The idea behind GoalDebug is to allow the spreadsheet user to express expectations about cell values using a simple interface, as shown in the following figure.

The system uses this information to compare the expected data with the data computed by formulas in the spreadsheet and creates a ranked list of suggestions for how to change cell formulas.

Each of these changes, if applied, will have the effect that the expected value will indeed be computed in the specified cell. The application of a change can be done completely automatically, which reduces the risk of introducing new errors into the spreadsheet. Still the user is given the option to manually edit the changed formula before it is applied to the spreadsheet.

A more detailed description of the GoalDebug system is given in this paper. In particular, the first three pages (Sections 1 and 2) provide a detailed explanation of what the system can do.

How well does GoalDebug perform?

To investigaste the effectiveness of the suggestions produced by GoalDebug, we introduced errors into spreadsheets and then checked whether GoalDebug was able to produce the correct change suggestion, and if so, how high it was ranked. We found, for example, that in 80% of the cases the correct suggestion is among the top five suggestions, that is, in 80% of the cases will a user be able to fix a spreadsheet bug by only having to inspect five suggestions. The red graph in the figure below illustrates the performance of GoalDebug.

The graph shows that the top-ranked suggestion corrects the error in 59% of the cases, one of the top two suggestions correct it in 71% of the cases, and so on. (The cyan graph shows the performance of an older version of the system.) Details about this study can be found in our 2007 ICSE paper, see below.

Further Information

Test-Driven Goal-Directed Debugging in Spreadsheets, Robin Abraham and Martin Erwig
IEEE Int. Symp. on Visual Languages and Human-Centric Computing, 131-138, 2008

GoalDebug: A Spreadsheet Debugger for End Users, Robin Abraham and Martin Erwig
29th IEEE/ACM Int. Conf. on Software Engineering, 251-260, 2007

Goal-Directed Debugging of Spreadsheets, Robin Abraham and Martin Erwig
IEEE Int. Symp. on Visual Languages and Human-Centric Computing, 37-44, 2005

Contact

For more information, please contact: Martin Erwig