Automated Software Engineering

, Volume 23, Issue 1, pp 105–144 | Cite as

Model-based diagnosis of spreadsheet programs: a constraint-based debugging approach

  • Dietmar Jannach
  • Thomas Schmitz


Spreadsheet programs are probably the most successful example of end-user software development tools and are used for a variety of purposes. Like any type of software, they are prone to error, in particular as they are usually developed by non-programmers. While various techniques exist to support the developer in finding errors in procedural programs, the tool support for spreadsheet debugging is still limited. In this paper, we show how techniques from model-based diagnosis can be applied and extended for spreadsheet debugging by translating the relevant parts of a spreadsheet to a constraint satisfaction problem. We additionally propose both problem-specific and generalizable extensions to the classical diagnosis algorithms which help to detect potential problems in a spreadsheet based on user-provided test cases more efficiently. The proposed techniques were integrated into a modular framework for spreadsheet debugging and evaluated with respect to scalability based on a number of real-world and artificially created spreadsheets. An additional error detection exercise involving 24 subjects was performed to assess the general applicability of such advanced spreadsheet debugging techniques for end users.


Model-based diagnosis Constraints Spreadsheet programs 



This work was partially supported by the European Union through the programme “Europäischer Fonds für regionale Entwicklung—Investition in unsere Zukunft” under contract number 300251802.


  1. Abraham, R., Erwig, M.: AutoTest: a tool for automatic test case generation in spreadsheets. In: Proceedings of the IEEE Symposium on Visual Languages and Human-Centric Computing (VL/HCC 2006), Brighton, United Kingdom, pp. 43–50 (2006)Google Scholar
  2. Abraham, R., Erwig, M.: Inferring templates from spreadsheets. In: Proceedings of the 28th International Conference on Software Engineering (ICSE 2006), Shanghai, China, pp. 182–191 (2006)Google Scholar
  3. Abraham, R., Erwig, M.: GoalDebug: a spreadsheet debugger for end users. In: Proceedings of the 29th International Conference on Software Engineering (ICSE 2007), Minneapolis, MN, USA, pp. 251–260 (2007)Google Scholar
  4. Abraham, R., Erwig, M.: Mutation operators for spreadsheets. IEEE Trans. Softw. Eng. 35(1), 94–108 (2009)CrossRefGoogle Scholar
  5. Abreu, R., Zoeteweij, P., van Gemund, A.J.C.: An observation-based model for fault localization. In: Proceedings of the 6th International Workshop on Dynamic Analysis (WODA 2008), New York, NY, USA, pp. 64–70 (2008)Google Scholar
  6. Abreu, R., Riboira, A., Wotawa, F.: Constraint-based debugging of spreadsheets. In: Proceedings of the 15th Ibero-American Conference on Software Engineering (CIbSE 2012), Buenos Aires, Argentina, pp. 1–14 (2012)Google Scholar
  7. Asavametha, A.: Detecting bad smells in spreadsheets. Master’s thesis, School of Electrical Engineering and Computer Science, Oregon State University, USA (2012)Google Scholar
  8. Brown, P.S., Gould, J.D.: An experimental study of people creating spreadsheets. ACM Trans. Inf. Syst. 5(3), 258–272 (1987)CrossRefGoogle Scholar
  9. Burnett, M., Sheretov, A., Ren, B., Rothermel, G.: Testing homogeneous spreadsheet grids with the “what you see Is what you test” methodology. IEEE Trans. Softw. Eng. 28(6), 576–594 (2002)CrossRefGoogle Scholar
  10. Burnett, M., Cook, C., Pendse, O., Rothermel, G., Summet, J., Wallace, C.: End-user software engineering with assertions in the spreadsheet paradigm. In: Proceedings of the 25th International Conference on Software Engineering (ICSE 2003), Portland, Oregon, pp. 93–103 (2003)Google Scholar
  11. Campos, J., Riboira, A., Perez, A., Abreu, R.: Gzoltar: an eclipse plug-in for testing and debugging. In: Proceedings of the 27th IEEE/ACM International Conference on Automated Software Engineering (ASE 2012), Essen, Germany, pp. 378–381 (2012)Google Scholar
  12. Chadwick, D., Knight, B., Rajalingham, K.: Quality control in spreadsheets: a visual approach using color codings to reduce errors in formulae. Softw. Qual. Control 9(2), 133–143 (2001)CrossRefGoogle Scholar
  13. Chittaro, L., Ranon, R.: Hierarchical model-based diagnosis based on structural abstraction. Artif. Intell. 155(1–2), 147–182 (2004)zbMATHMathSciNetCrossRefGoogle Scholar
  14. Console, L., Friedrich, G., Dupré, D.T.: Model-based diagnosis meets error diagnosis in logic programs. In: Proceedings of the 13th International Joiunt Conference on Artificial Intelligence (IJCAI 1993), Chambéry, France, pp. 1494–1499 (1993)Google Scholar
  15. Creeth, R.: Micro-computer spreadsheets: their uses and abuses. J. Account. 159(6), 90–93 (1985)Google Scholar
  16. Cunha, J., Fernandes, JaP., Ribeiro, H., Saraiva, Ja.: Towards a catalog of spreadsheet smells. In: Proceedings of the 12th International Conference on Computational Science and Its Applications (ICCSA 2012), Salvador de Bahia, Brazil, pp. 202–216 (2012)Google Scholar
  17. de Kleer, J.: Using crude probability estimates to guide diagnosis. Artif. Intell. 45(3), 381–391 (1990)CrossRefGoogle Scholar
  18. de Kleer, J., Williams, B.C.: Diagnosing multiple faults. Artif. Intell. 32(1), 97–130 (1987)zbMATHCrossRefGoogle Scholar
  19. Ditlea, S.: Spreadsheets can be hazardous to your health. Pers. Comput. 11(1), 60–69 (1987)Google Scholar
  20. Felfernig, A., Schubert, M.: FastDiag: a diagnosis algorithm for inconsistent constraint sets. In: Proceedings of the 21st International Workshop on the Principles of Diagnosis (DX 2010), Portland, OR, USA, pp. 31–38 (2010)Google Scholar
  21. Felfernig, A., Friedrich, G., Jannach, D., Stumptner, M., Zanker, M.: Hierarchical diagnosis of large configurator knowledge bases. In: Proceedings of the Joint German/Austrian Conference on Artificial Intelligence (KI 2001), Vienna, Austria, pp. 185–197 (2001)Google Scholar
  22. Felfernig, A., Friedrich, G., Jannach, D., Stumptner, M.: Consistency-based diagnosis of configuration knowledge bases. Artif. Intell. 152(2), 213–234 (2004)zbMATHMathSciNetCrossRefGoogle Scholar
  23. Filby, G. (ed.): Spreadsheets in Science and Engineering. Springer, Berlin (1998)Google Scholar
  24. Freuder, E.C., Wallace, R.J., Heffernan, R.: Partial constraint satisfaction. Artif. Intell. 58(1–3), 278–283 (1992)Google Scholar
  25. Friedrich, G., Nejdl, W.: Choosing observations and actions in model-based diagnosis/repair systems. In: Proceedings of the 3rd International Conference on Principles of Knowledge Representation and Reasoning (KR 1992), Cambridge, MA, USA, pp. 489–498 (1992)Google Scholar
  26. Friedrich, G., Stumptner, M., Wotawa, F.: Model-based diagnosis of hardware designs. Artif. Intell. 111(1–2), 3–39 (1999)zbMATHMathSciNetCrossRefGoogle Scholar
  27. Galletta, D.F., Abraham, D., Louadi, M.E., Lekse, W., Pollalis, Y.A., Sampler, J.L.: An empirical study of spreadsheet error-finding performance. Account. Manag. Inf. Technol. 3(2), 79–95 (1993)CrossRefGoogle Scholar
  28. Greiner, R., Smith, B.A., Wilkerson, R.W.: A correction to the algorithm in Reiter’s theory of diagnosis. Artif. Intell. 41(1), 79–88 (1989)zbMATHMathSciNetCrossRefGoogle Scholar
  29. Hermans, F.: Improving spreadsheet test practices. In: Proceedings of the 23rd Annual International Conference on Computer Science and Software Engineering (CASCON 2013), Markham, Ontario, Canada, pp. 56–69 (2013)Google Scholar
  30. Hermans, F., Pinzger, M., van Deursen, A.: Detecting and visualizing inter-worksheet smells in spreadsheets. In: Proceedings of the 34th International Conference on Software Engineering (ICSE 2012), Zurich, Switzerland, pp. 441–451 (2012)Google Scholar
  31. Hermans, F., Pinzger, M., van Deursen, A.: Detecting code smells in spreadsheet formulas. In: Proceedings of the 28th IEEE International Conference on Software Maintenance (ICSM 2012), pp. 409–418. Trento, Riva del Garda (2012)Google Scholar
  32. Herndon, T., Ash, M., Pollin, R.: Does high public debt consistently stifle economic growth? A critique of Reinhart and Rogoff. Working paper 322, Political Economy Research Institute, University of Massachusetts, Amherst (2013)Google Scholar
  33. Hofer, B., Riboira, A., Wotawa, F., Abreu, R., Getzner, E.: On the empirical evaluation of fault localization techniques for spreadsheets. In: Proceedings of the 16th International Conference on Fundamental Approaches to Software Engineering (FASE 2013), Rome, Italy, pp. 68–82 (2013)Google Scholar
  34. Jannach, D., Engler, U.: Toward model-based debugging of spreadsheet programs. In: Proceedings of the 9th Joint Conference on Knowledge-Based Software Engineering (JCKBSE 2010), Kaunas, Lithuania, pp. 252–264 (2010)Google Scholar
  35. Jannach, D., Baharloo, A., Williamson, D.: Toward an integrated framework for declarative and interactive spreadsheet debugging. In: Procedings of the 8th International Conference on Evaluation of Novel Approaches to Software Engineering (ENASE 2013), Angers, France, pp. 117–124 (2013)Google Scholar
  36. Jones, J.A., Harrold, M.J., Stasko, J.: Visualization of test information to assist fault localization. In: Proceedings of the 24th International Conference on Software Engineering (ICSE 2002), Orlando, FL, USA, pp. 467–477 (2002)Google Scholar
  37. Junker, U.: QUICKXPLAIN: preferred explanations and relaxations for over-constrained problems. In: Proceedings of the 19th National Conference on Artificial Intelligence (AAAI 2004), San Jose, CA, USA, pp. 167–172 (2004)Google Scholar
  38. Larrosa, J., Meseguer, P., Schiex, T.: Maintaining reversible DAC for Max-CSP. Artif. Intell. 107(1), 149–163 (1999)Google Scholar
  39. Mateis, C., Stumptner, M., Wieland, D., Wotawa, F.: Model-based debugging of Java Programs. In: Proceedings of the Fourth International Workshop on Automated Debugging (AADEBUG 2000), Munich, Germany (2000)Google Scholar
  40. Metodi, A., Stern, R., Kalech, M., Codish, M.: Compiling model-based diagnosis to Boolean satisfaction. In: Proceedings of the 26th AAAI Conference on Artificial Intelligence (AAAI 2012), Toronto, Canada (2012)Google Scholar
  41. Panko, R.R.: What we know about spreadsheet errors. J. End User Comput. 10(2), 15–21 (1998)CrossRefGoogle Scholar
  42. Panko, R.R., Halverson, R.P.: Spreadsheets on trial: a survey of research on spreadsheet risks. In: Proceedings of the 29th Hawaii International Conference on System Sciences (HICSS 1996), Wailea, HI, USA, pp. 326–335 (1996)Google Scholar
  43. Panko, R.R., Port, D.N.: End user computing: the dark matter (and dark energy) of corporate IT. In: Proceedings of the 45th Hawaii International Conference on System Sciences (HICSS 2012), Wailea, HI, USA, pp. 4603–4612 (2012)Google Scholar
  44. Parnin, C., Orso, A.: Are automated debugging techniques actually helping programmers?. In: Proceedings of the 2011 International Symposium on Software Testing and Analysis (ISSTA 2011), Toronto, Canada, pp. 199–209 (2011)Google Scholar
  45. Pemberton, J., Robson, A.: Spreadsheets in business. Ind. Manag. Data Syst. 100(8), 379–388 (2000)CrossRefGoogle Scholar
  46. Powell, S.G., Baker, K.R., Lawson, B.: A critical review of the literature on spreadsheet errors. Decis. Support Syst. 46(1), 128–138 (2008)CrossRefGoogle Scholar
  47. Reichwein, J., Rothermel, G., Burnett, M.: Slicing spreadsheets: an integrated methodology for spreadsheet testing and debugging. In: Proceedings of the 2nd Conference on Domain-Specific Languages (DSL 1999), Austin, Texas, pp. 25–38 (1999)Google Scholar
  48. Reinhart, C.M., Rogoff, K.S.: Growth in a time of debt. Am. Econ. Rev. 100(2), 573–578 (2010)CrossRefGoogle Scholar
  49. Reiter, R.: A theory of diagnosis from first principles. Artif. Intell. 32(1), 57–95 (1987)zbMATHMathSciNetCrossRefGoogle Scholar
  50. Rothermel, G., Li, L., Dupuis, C., Burnett, M.: What you see is what you test: a methodology for testing form-based visual programs. In: Proceedings of the 20th International Conference on Software Engineering (ICSE 1998), Kyoto, Japan, pp. 198–207 (1998)Google Scholar
  51. Shchekotykhin, K., Friedrich, G., Fleiss, P., Rodler, P.: Interactive ontology debugging: two query strategies for efficient fault localization. J. Web Semant. 12–13, 88–103 (2012)CrossRefGoogle Scholar
  52. Smith, B.M.: Locating the phase transition in binary constraint satisfaction problems. Artif. Intell. 81, 155–181 (1994)CrossRefGoogle Scholar
  53. Stumptner, M., Wotawa, F.: Model-based program debugging and repair. In: Proceedings of the 9th International Industrial and Engineering Applications of Artificial Intelligence and Expert Systems (IEA/AIE 1996), Fukuoka, Japan, pp. 155–160 (1996)Google Scholar
  54. Stumptner, M., Wotawa, F.: Debugging functional programs. In: Proceedings of the 16th International Joint Conference on Artificial Intelligence (IJCAI 1999), Stockholm, Sweden, pp. 1074–1079 (1999)Google Scholar
  55. Stumptner, M., Wotawa, F.: Diagnosing tree-structured systems. Artif. Intell. 127(1), 1–29 (2001)zbMATHMathSciNetCrossRefGoogle Scholar
  56. Tsang, E.: Foundations of Constraint Satisfaction. Academic Press, London (1993)Google Scholar
  57. Wotawa, F., Pill, I.: On classification and modeling issues in distributed model-based diagnosis. AI Commun. 26(1), 133–143 (2013)MathSciNetGoogle Scholar
  58. Wotawa, F., Weber, J.: Challenges of distributed model-based diagnosis. In: Trends in Applied Intelligent Systems, Springer, Lecture Notes in Computer Science, pp. 711–720 (2010)Google Scholar

Copyright information

© Springer Science+Business Media New York 2014

Authors and Affiliations

  1. 1.TU DortmundDortmundGermany

Personalised recommendations