Investigating Effects of Common Spreadsheet Design Practices on Correctness and Maintainability

The main reserach question is “Can best practices serve as a quality indicator?”, where the authors define quality as both correctness and maintainability.

Correctness
The authors define correctness as degree to which the output of the spreadsheet conform to  given test scenarios.

Maintainability
Here they use the sum of factors that contribute to the chance a spreadsheet remains correct under modification.

The best practices under review are:

  • Avoiding constants in formulas
  • Low complexity of formulas
  • Formulas should refer to left and above

Experiment
The performed an experiment in two phases. In phase 1, participants created spreadsheets and in the second phase other participants performed maintenance on those sheets. Participants had to create a spreadsheet to compare passenger cars, the data used stems from reviews in car magazines, divided over different categories like costs and comfort. Additional requirements for the maintenance included adding extra reviews, adding an extra variable that impacts two categories and visually showing the winner car.

Next, they tried to find defects in the resulting spreadsheets with the Spreadsheet Inspection Framework, counted the number of violations of best practices (including increase/decrease between phase 1 and 2) and compared those.

 

Results
The authors make some interesting observations. Firstly, they showed that the original author is not more likely to perform better in maintenance  on a spreadsheet than a random author. Also in general, people were more likely to fail in the second task (independent of being the author).

Furthermore, the number of complex formulas seems not to be a predictor of correct results, the two spreadsheets with the lowest number of complex formulas were both incorrect. The other way around does also not hold, we cannot select correct spreadsheets by looking at sheets that do not violate the best practices.

However,  there is also good news: spreadsheets that conform to best practices do have a higher chance at being maintained in a good way (leading to success) The spreadsheet with the lowest number of defects was the one that conformed the best to best practices.