Skip to main content
SpringerLink
Log in

On the empirical evaluation of similarity coefficients for spreadsheets fault localization

  • Published:
Automated Software Engineering Aims and scope Submit manuscript

Abstract

Spreadsheets are by far the most prominent example of end-user programs of ample size and substantial structural complexity. They are usually not thoroughly tested so they often contain faults. Debugging spreadsheets is a hard task due to the size and structure, which is usually not directly visible to the user, i.e., the functions are hidden and only the computed values are presented. A way to locate faulty cells in spreadsheets is by adapting software debugging approaches for traditional procedural or object-oriented programming languages. One of such approaches is spectrum-based fault localization (Sfl). In this paper, we study the impact of different similarity coefficients on the accuracy of Sfl applied to the spreadsheet domain. Our empirical evaluation shows that three of the 42 studied coefficients (Ochiai, Jaccard and Sorensen-Dice) require less effort by the user while inspecting the diagnostic report, and can also be used interchangeably without a loss of accuracy. In addition, we illustrate the influence of the number of correct and incorrect output cells on the diagnostic report.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7

Similar content being viewed by others

Notes

  1. Known as iterative calculations; see http://office.microsoft.com/en-us/excel-help/remove-or-allow-a-circular-reference-HP010066243.aspx

  2. As already mentioned, coincidental correct output cells have a negative impact on the ranking of the faulty cell.

References

  • Abraham, R., Erwig, M.: Goal-directed debugging of spreadsheets. In: Proceedings of the 2005 IEEE Symposium on Visual Languages and Human-Centric Computing. VLHCC ’05, pp. 37–44. IEEE Computer Society, Washington, DC, USA (2005)

  • Abraham, R., Erwig, M.: Autotest: a tool for automatic test case generation in spreadsheets. In: Proceedings of the 2006 IEEE Symposium on Visual Languages and Human-Centric Computing. VLHCC ’06, pp. 43–50. Brighton, UK (2006)

  • Abraham, R., Erwig, M.: Goaldebug: a spreadsheet debugger for end users. In: 29th IEEE International Conference on Software Engineering, pp. 251–260 (2007)

  • Abraham, R., Erwig, M.: Ucheck: a spreadsheet type checker for end users. J. Vis. Lang. Comput. 18, 71–95 (2007). doi:10.1016/j.jvlc.2006.06.001

    Article  Google Scholar 

  • Abraham, R., Erwig, M.: Mutation operators for spreadsheets. IEEE Trans. Softw. Eng. 35(1), 94–108 (2009)

    Article  Google Scholar 

  • Abraham, R., Erwig, M.: Personal communication (2013)

  • Abreu, R., Riboira, A., Wotawa, F.: Constraint-based debugging of spreadsheets. In: Proceedings of the 15th Ibero-American Conference on Software Engineering (2012)

  • Abreu, R., Zoeteweij, P., van Gemund, A.: An evaluation of similarity coefficients for software fault localization. In: Proceedings of the 12th Pacific Rim International Symposium on Dependable Computing, PRDC ’06, pp. 39–46. IEEE Computer Society, Washington, DC, USA (2006). doi:10.1109/PRDC.2006.18

  • Abreu, R., Zoeteweij, P., van Gemund, A.J.C.: On the accuracy of spectrum-based fault localization. In: Proceedings of the Testing: Academic and Industrial Conference Practice and Research Techniques—MUTATION, TAICPART-MUTATION ’07, pp. 89–98. IEEE Computer Society, Washington, DC, USA (2007). http://dl.acm.org/citation.cfm?id=1308173.1308264

  • Abreu, R., Zoeteweij, P., Golsteijn, R., Van Gemund, A.J.: A practical evaluation of spectrum-based fault localization. J. Syst. Softw. 82(11), 1780–1792 (2009)

    Article  Google Scholar 

  • Aggarwal, C.C., Yu, P.S.: A new framework for itemset generation. In: Proceedings of the 17th ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems (PODS’98), pp. 18–24 (1998)

  • Agrawal, R., Srikant, R.: Fast algorithms for mining association rules. In: Proceedings of 20th International Conference on Very Large Data, Bases (VLDB’94), pp. 487–499 (1994)

  • Agresti, A.: An Introduction to Categorical Data Analysis. Wiley, New York (1996)

    MATH  Google Scholar 

  • Anderberg, M.R.: Cluster Analysis for Applications. Technical Report. Academic Press, London (1973)

  • Ayalew, Y., Mittermeir, R.: Spreadsheet debugging. Bilding Better Business Spreadsheets—from the ad-hoc to the quality-engineered. In: Proceedings of EuSpRIG 2003, Dublin, Ireland, 24–25 July 2003, pp. 67–79 (2003)

  • Bregar, A.: Complexity metrics for spreadsheet models. The Computing Research Repository (CoRR) abs/0802.3895 (2008). http://arxiv.org/abs/0802.3895

  • Brglez, F., Fujiwara, H.: A neutral netlist of 10 combinational benchmark circuits and a target translator in fortran. In: Proceedings of the IEEE International Symposium on Circuits and Systems, pp. 663–698 (1985)

  • Brin, S., Motwani, R., Ullman, J.D., Tsur, S.: Dynamic itemset counting and implication rules for market basket data. SIGMOD Rec. 26(2), 255–264 (1997)

    Article  Google Scholar 

  • Burnett, M.M., Cook, C.R., Pendse, O., Rothermel, G., Summet, J., Wallace, S.: End-user software engineering with assertions in the spreadsheet paradigm. In: Clarke L.A., Dillon L., Tichy W.F. (eds.) ICSE, pp. 93-105. IEEE Computer Society (2003)

  • 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, pp. 378–381. ACM, New York, NY, USA (2012). doi:10.1145/2351676.2351752

  • Chadwick, D., Knight, B., Rajalingham, K.: Quality control in spreadsheets: a visual approach using color codings to reduce errors in formulae. Softw. Qual. J. 9(2), 133–143 (2001)

    Article  Google Scholar 

  • Cheng, H., Lo, D., Zhou, Y., Wang, X., Yan, X.: Identifying bug signatures using discriminative graph mining. In: Proceedings of the 18th International Symposium on Software Testing and Analysis (ISSTA ’09), pp. 141–152 (2009)

  • Clark, P., Boswell, R.: Rule induction with cn2: some recent improvements. In: EWSL, Lecture Notes in Computer Science, vol 482, pp. 151–163. Springer (1991)

  • Coblenz, M.J., Ko, A.J., Myers, B.A.: Using objects of measurement to detect spreadsheet errors. In: VL/HCC, pp. 314–316. IEEE Computer Society (2005). doi:10.1109/VLHCC.2005.67

  • Cohen, J.: A coefficient of agreement for nominal scales. Educ. Psychol. Meas. 20(1), 37–46 (1960)

    Article  Google Scholar 

  • Cunha, J., Fernandes, J.P., Mendes, J., Saraiva, J.: Mdsheet: a framework for model-driven spreadsheet engineering. In: Glinz M., Murphy G.C., Pezzè M. (eds.) ICSE, pp. 1395–1398. IEEE (2012)

  • Dice, L.R.: Measures of the amount of ecologic association between species. Ecology 26(3), 297–302 (1945)

    Article  Google Scholar 

  • Fisher, M., Cao, M., Rothermel, G., Cook, C.R., Burnett, M.M.: Automated test case generation for spreadsheets. In: Proceedings of the 24th International Conference on Software Engineering, ICSE ’02, pp. 141–151. ACM Press (2002)

  • Fisher, M.I., Rothermel, G.: The EUSES spreadsheet corpus: a shared resource for supporting experimentation with spreadsheet dependability mechanisms. In: 1st Workshop on End-User, Software Engineering, pp. 47–51 (2005)

  • Geng, L., Hamilton, H.J.: Interestingness measures for data mining: a survey. ACM Comput. Surv. 38(3), 1–32 (2006)

    Article  Google Scholar 

  • Goodman, L.A., Kruskal, W.H.: Measures of association for cross classifications. J. Am. Stat. Assoc. 49(268), 732–764 (1954)

    MATH  Google Scholar 

  • Hand, D.J., Smyth, P., Mannila, H.: Principles of Data Mining. MIT Press, Cambridge (2001)

    Google Scholar 

  • Healey, J.: Statistics: A Tool for Social Research. Wadsworth Publishing, Belmont (1993)

    Google Scholar 

  • Hermans, F., Pinzger, M., van Deursen, A.: Automatically extracting class diagrams from spreadsheets. In: D’Hondt T. (ed.) ECOOP, Lecture Notes in Computer Science, vol 6183, pp. 52–75. Springer (2010). doi:10.1007/978-3-642-14107-2_4

  • Hermans, F., Pinzger, M., van Deursen, A.: Breviz: visualizing spreadsheets using dataflow diagrams. The Computing Research Repository (CoRR) abs/1111.6895 (2011). http://arxiv.org/abs/1111.6895

  • Hermans, F., Pinzger, M., van Deursen, A.: Detecting code smells in spreadsheet formulas. In: ICSM, pp. 409–418. IEEE Computer Society (2012a)

  • Hermans, F., Pinzger, M., van Deursen, A.: Measuring spreadsheet formula understandability. The Computing Research Repository (CoRR) abs/1209.3517 (2012b)

  • Hermans, F., Pinzger, M., van Deursen, A.: Detecting and visualizing inter-worksheet smells in spreadsheets. In: Proceedings of the 2012 International Conference on Software Engineering, pp. 441–451. IEEE Press (2012c)

  • Hermans, F., Sedee, B., Pinzger, M., van Deursen, A.: Data clone detection and visualization in spreadsheets. In: Notkin D., Cheng B.H.C., Pohl K. (eds.) ICSE, pp. 292–301. IEEE / ACM (2013). http://dl.acm.org/citation.cfm?id=2486827

  • Hofer, B., Riboira, A., Wotawa, F., Abreu, R., Getzner, E.: On the empirical evaluation of fault localization techniques for spreadsheets. In: Cortellessa, V., Varró, D. (eds.) FASE, Lecture Notes in Computer Science, pp. 68–82. Springer (2013)

  • Jannach, D., Baharloo, A., Williamson, D.: Toward an integrated framework for declarative and interactive spreadsheet debugging. In: 6th International Conference Evaluation of Novel Approaches to Software Engineering (ENASE), Angers, France, pp. 117–124 (2013)

  • Jannach, D., Engler, U.: Toward model-based debugging of spreadsheet programs. In: 9th Joint Conference on Knowledge-Based Software Engineering (JCKBSE’10) 25–27 Aug 2010, Kaunas, Lithuania pp. 252–264 (2010)

  • Janssen, T., Abreu, R., van Gemund, A.: Zoltar: A toolset for automatic fault localization. In: Proceedings of the 2009 IEEE/ACM International Conference on Automated Software Engineering, ASE ’09, pp. 662–664. IEEE Computer Society, Washington, DC, USA (2009). doi:10.1109/ASE.2009.27

  • Jones, J.A., Harrold, M.J.: Empirical evaluation of the tarantula automatic fault-localization technique. In: Proceedings of the 20th IEEE/ACM international Conference on Automated software engineering, ASE ’05, pp. 273–282 (2005)

  • Klösgen, W.: Explora: a multipattern and multistrategy discovery assistant. In: Fayyad, U.M., Piatetsky-Shapiro, G., Smyth, P., Uthurusamy, R. (eds.) Advances in Knowledge Discovery and Data Mining, pp. 249–271. American Association for Artificial Intelligence, Menlo Park (1996)

    Google Scholar 

  • Ko, A.J., Abraham, R., Beckwith, L., Blackwell, A., Burnett, M., Erwig, M., Scaffidi, C., Lawrance, J., Lieberman, H., Myers, B., Rosson, M.B., Rothermel, G., Shaw, M., Wiedenbeck, S.: The state of the art in end-user software engineering. ACM Comput. Surv. 43(3), 1–44 (2011)

    Article  Google Scholar 

  • Lo, D., Jiang, L., Budi, A., et al.: Comprehensive evaluation of association measures for fault localization. In: 2010 IEEE International Conference on Software Maintenance (ICSM), pp. 1–10. IEEE (2010)

  • Lucia, Lo, D., Jiang, L., Thung, F., Budi, A.: Extended comprehensive study of association measures for fault localization. J. Softw. (2013). doi:10.1002/smr.1616

  • MacQueen, J.: Some methods for classification and analysis of multivariate observations. In: Proceedings of the 5th Berkeley Symposium on Mathematical Statistics and Probability, University of California 1965/66, 1, pp. 281–297 (1967)

  • Mittermeir, R., Clermont, M.: Finding high-level structures in spreadsheet programs. In: van Deursen, A., Burd, E. (eds.) WCRE, pp. 221–232. IEEE Computer Society (2002). http://ieeexplore.ieee.org/xpls/abs_all.jsp?arnumber=1173080&tag=1

  • Nica, I., Pill, I., Quaritsch, T., Wotawa, F.: The route to success—a performance comparison of diagnosis algorithms. In: Rossi F. (ed.) IJCAI. IJCAI/AAAI (2013)

  • Ochiai, A.: Zoogeographic studies on the soleoid fishes found in japan and its neighbouring regions. Bull. Jpn. Soc. Sci. Fish. 22, 26–520 (1957)

    Google Scholar 

  • Ohsaki, M., Kitaguchi, S., Okamoto, K., Yokoi, H., Yamaguchi, T.: Evaluation of rule interestingness measures with a clinical dataset on hepatitis. In: Proceedings of the 8th European Conference on Principles and Practice of Knowledge Discovery in Databases. PKDD ’04, pp. 362–373. New York, NY, USA (2004)

  • Piatetsky-Shapiro, G.: Discovery, analysis, and presentation of strong rules. In: Piatetsky-Shapiro, G., Frawley, W.J. (eds.) Knowledge Discovery in Databases, pp. 229–248. AAAI/MIT Press, Menlo Park (1991)

    Google Scholar 

  • Rogers, D.J., Tanimoto, T.T.: A computer program for classifying plants. Science 132(3434), 1115–1118 (1960)

    Article  Google Scholar 

  • Rothermel, K.J., Cook, C.R., Burnett, M.M., Schonfeld, J., Green, T.R.G., Rothermel, G.: WYSIWYT testing in the spreadsheet paradigm: an empirical evaluation. In: Proceedings of ICSE’00, pp. 230–239. ACM (2000). http://doi.acm.org/10.1145/337180.337206

  • Ruthruff, J., Creswick, E., Burnett, M., Cook, C., Prabhakararao, S., Fisher II, M., Main, M.: End-user software visualizations for fault localization. In: Proceedings of the 2003 ACM symposium on Software visualization, SoftVis ’03, pp. 123–132. ACM, New York, NY, USA (2003). doi:10.1145/774833.774851

  • Shortliffe, E.H., Buchanan, B.G.: A model of inexact reasoning in medicine. Math. Biosci. 23(3–4), 351–379 (1975)

    Article  MathSciNet  Google Scholar 

  • Smyth, P., Goodman, R.: An information theoretic approach to rule induction from databases. IEEE Trans. Knowl. Data Eng. 4(4), 301–316 (1992)

    Article  Google Scholar 

  • Sokal, R.R., Michener, C.D.: A statistical method for evaluating systematic relationships. Univ. Kans. Sci. Bull. 38, 1409–1438 (1958)

    Google Scholar 

  • Tan, P.N., Kumar, V., Srivastava, J.: Selecting the right interestingness measure for association patterns. In: Proceedings of the 8th ACM SIGKDD International Conference on Knowledge Discovery and Data Mining (KDD’02), pp. 32–41 (2002)

  • Tikir, M.M., Hollingsworth, J.K.: Efficient instrumentation for code coverage testing. In: Proceedings of the 2002 ACM SIGSOFT International Symposium on Software Testing and Analysis, ISSTA ’02, pp. 86–96. ACM, New York, NY, USA (2002). doi:10.1145/566172.566186

  • Wang, X., Cheung, S.C., Chan, W.K., Zhang, Z.: Taming coincidental correctness: coverage refinement with context patterns to improve fault localization. In: ICSE, pp. 45–55. IEEE (2009)

  • Wilcoxon, F.: Individual comparisons by ranking methods. Biom. Bull. 1(6), 80–83 (1945)

    Article  Google Scholar 

  • Xu, X., Debroy, V., Wong, W.E., Guo, D.: Ties within fault localization rankings: exposing and addressing the problem. Int. J. Softw. Eng. Knowl. Eng. 21(6), 803–827 (2011)

    Article  Google Scholar 

Download references

Acknowledgments

This work was supported by the Foundation for Science and Technology (FCT), of the Portuguese Ministry of Science, Technology, and Higher Education (MCTES), under Project PTDC/EIA-CCO/108613/2008, and the competence network Softnet Austria II (www.soft-net.at, COMET K-Projekt) funded by the Austrian Federal Ministry of Economy, Family and Youth (bmwfj), the province of Styria, the Steirische Wirtschaftsförderungsgesellschaft mbH. (SFG), and the city of Vienna in terms of the center for innovation and technology (ZIT).

Author information

Authors and Affiliations

  1. Institute for Software Technology, Graz University of Technology, Graz, Austria

    Birgit Hofer & Franz Wotawa

  2. Department of Informatics Engineering, Faculty of Engineering, University of Porto, Porto, Portugal

    Alexandre Perez & Rui Abreu

Authors
  1. Birgit Hofer
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Alexandre Perez
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Rui Abreu
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Franz Wotawa
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Birgit Hofer.

Rights and permissions

Reprints and permissions

About this article

Cite this article

Hofer, B., Perez, A., Abreu, R. et al. On the empirical evaluation of similarity coefficients for spreadsheets fault localization. Autom Softw Eng 22, 47–74 (2015). https://doi.org/10.1007/s10515-014-0145-3

Download citation

  • Received:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s10515-014-0145-3

Keywords

Search

Navigation