Interactive Spreadsheet Debugging

Electronic spreadsheets, e.g., based on Microsoft Excel, are widely used in organizations for a variety of purposes like budgeting, forecasting, and financial planning. In many cases, the calculations within such spreadsheets are directly used for managerial decision-making. Errors in spreadsheets can therefore represent a major risk to businesses and there are plenty of examples where such errors have led to significant financial losses. Unfortunately, errors in spreadsheets are not uncommon, and studies show that the chance of a complex spreadsheet to contain at least one error is quite high. Several countermeasures can be applied to minimize the risk that errors remain in a spreadsheet before it is used. One specific approach is to provide the spreadsheet developer with better tools to test the spreadsheet for correctness and to find the specific location of those formulas that at the end lead to a wrong calculation outcome. Specifically for the latter task, the identification of faulty formulas, a number of algorithms were proposed that have the goal to determine a ranked list of suspicious formulas. Recent research in related fields however stipulates that presenting such a ranked list as the only debugging aid might not be enough to be truly useful for the spreadsheet developer. In this project, we will therefore explore new approaches for interactive spreadsheet debugging, building on the results of our previous research on algorithmic approaches to fault localization. Specifically, we will investigate approaches where the user is interactively guided by the system to the true fault location in an iterative process. Furthermore, we will explore explanation mechanisms, where users can ask why and why not certain calculation outcomes are observed. To achieve these goals and real-time interaction, we will additionally explore improvements to the underlying algorithms. In line with current streams of research in general software engineering, we will empirically validate our research approaches not only based on simulation experiments, but with the help of different user studies. As an additional result, we will obtain new insights about the general behavior and strategies of users when they design, test, and debug their spreadsheets.

Electronic spreadsheets, e.g., based on Microsoft Excel, are widely used in organizations for a variety of purposes like budgeting, forecasting, and financial planning. In many cases, the calculations within such spreadsheets are directly used for managerial decision-making. Errors in spreadsheets can therefore represent a major risk to businesses and there are plenty of examples where such errors have led to significant financial losses. Unfortunately, errors in spreadsheets are not uncommon, and studies show that the chance of a complex spreadsheet to contain at least one error is quite high. Several countermeasures can be applied to minimize the risk that errors remain in a spreadsheet before it is used. One specific approach is to provide the spreadsheet developer with better tools to test the spreadsheet for correctness and to find the specific location of those formulas that at the end lead to a wrong calculation outcome. Specifically for the latter task, the identification of faulty formulas, a number of algorithms were proposed that have the goal to determine a ranked list of "suspicious" formulas. Recent research in related fields however stipulates that presenting such a ranked list as the only debugging aid might not be enough to be truly useful for the spreadsheet developer. In this project, new approaches to interactive debugging of spreadsheets were investigated. Specifically, algorithms were developed in which the user is guided by the system in an iterative process to the true cause of the error in an interactive way. Additionally, explanation mechanisms were designed that allow the user to understand why a formula was marked by the system as potentially erroneous. In the field of algorithmic research, approaches from classical artificial intelligence as well as new methods based on machine learning were designed or further developed. The research aimed at increasing the accuracy of error prediction methods and reducing the required computational effort. The research results achieved were successfully published in reputable scientific outlets in both the field of artificial intelligence and software engineering. As purely data- or simulation-based approaches only provide partial insights into the utility of a debugging tool, two important user studies were conducted within the project. These studies yielded valuable insights into how users interact with such tools. It was particularly noticeable that some users overly relied on the tool and focused too much on the errors it highlighted as potentially faulty. The second study revealed that providing explanations to users during error analysis can be helpful. The results of these studies were published in two articles in a reputable journal in the field of software engineering.