Fixing spreadsheet errors

Computer scientists at Oregon State University have created a new, much simpler approach to fixing errors in spreadsheets, a system that is easy to use and might help businesses around the world reduce mistakes and save billions of dollars.

The advances - which allow a non-specialist to identify and fix a problem by selecting from a short list of change suggestions – were announced at the International Conference on Software Engineering in Minneapolis. The new system, called "GoalDebug," is being licensed to a spinoff company in Oregon.

Spreadsheets, a standard bookkeeping and accounting tool used by businesses to track everything from payroll to accounts receivable, are one of the most common of all computer software programs. But they are notoriously prone to errors, experts say.

‘Most users of spreadsheets are overconfident, they believe that the data is correct,’ said Martin Erwig, an associate professor of computer science in the OSU College of Engineering. ‘But it has been observed that up to 90 percent of the spreadsheets being used have non-trivial errors in them.’

What the new OSU systems do is try to identify the ways that humans commonly make mistakes and then suggest what the correct approach might have been. For instance, if someone sees a figure in a spreadsheet that seems suspicious or is clearly incorrect, they can plug in the correct number, and the OSU system can suggest several programming mistakes that might have created the error - which the user can then sort through and use to identify the problem.

A study performed by Robin Abraham, a recent OSU doctoral graduate, has shown that in 80 percent of the cases, the needed change is among the top five suggestions produced by the system, and in 72 percent of the cases it is among the top two suggestions.

‘With this approach, people still have ultimate control over the spreadsheet programs,’ Erwig said. ‘What we do is give them a prioritized list of where the problem most likely is. They can literally start at the top of the list, see if that's what went wrong, and if not, move down to the second option on the list.’

OSU's GoalDebug system, which stands for ‘Goal Directed Debugging of Spreadsheets,’ gives end users a chance to explore, apply, refine, or reject suggested changes. This much simpler and systematic approach allows people with comparatively little training in computer programming to identify and repair errors, Erwig said, by looking at a short list of possible problems instead of combing through hundreds or thousands of cells.

Software engineers spend 70-80 percent of their time testing and debugging programs, a recent study concluded - and even for these professionals, finding and fixing errors took an average of 17 hours.

‘Spreadsheet debugging problems are huge,’ Erwig said. ‘We believe there will be a significant demand for these products, a large market.’

The work, he said, is one part of the EUSES Consortium - End Users Shaping Effective Software - that OSU leads, in collaboration with Cambridge University, Carnegie Mellon University, Drexel University, IBM, Pennsylvania State University, and the University of Nebraska.