28.8. Errors in spreadsheets – is it time to change the way spreadsheets are developed?
Public examination of a doctoral dissertation in the field of Computer Science
Doctoral candidate: MSc Bennet Kankuzi
Date and venue: 28.8.2015, at 12 noon, Louhela auditorium, Joensuu Science Park
Language of the dissertation: English
Language of the public examination: English
Understanding what spreadsheet creators think (mental models), when performing various spreadsheet activities, could be key to dealing with the long standing problem of errors in spreadsheets. This is according to results from the doctoral research work by MSc Bennett Kankuzi of the University of Eastern Finland, School of Computing.
Millions of people, globally, use spreadsheets in their daily work but most non-trivial spreadsheets have been found to have costly errors. It is also documented that most spreadsheet errors involve construction and use of formulae.
In the research, three empirical studies were conducted and two Microsoft Excel add-ons were developed.The results of the studies indicate that with traditional spreadsheets, spreadsheet creators have to link up concepts from their application domain to low-level spreadsheet concepts such as cell references in order for them explain a spreadsheet as well as to locate and fix errors in spreadsheets. Therefore, a Microsoft Excel add-on that translates traditional spreadsheet formulae into application domain concepts was developed so that spreadsheet creators could map easily application domain concepts to their spreadsheet-specific counterparts. The add-on also automatically highlights all cells referenced in a translated spreadsheet formula.
The add-on was evaluated and found to help spreadsheet creators to locate more errors in their spreadsheets. Moreover, it also helped spreadsheet creators to think more in terms of the application domain rather than in terms of spreadsheet specific concepts. This is important as spreadsheet creators are not professional programmers but are rather experts in their application domains such as accounting and as such they are more familiar with application domain concepts rather than low-level spreadsheet specific concepts.
Another Microsoft Excel add-on was also developed such that, instead of using cell references as in traditional spreadsheets, spreadsheet creators could now debug their spreadsheets using application domain concepts only. An evaluation of this add-on showed that it correspondingly promoted spreadsheet creators to think more in terms of their application domain such that they almost no longer thought of their spreadsheet in terms of spreadsheet-specific low-level concepts. This implies that we can have a new spreadsheet paradigm in which spreadsheet creators would develop their spreadsheets in the usual ad-hoc, incremental manner but instead of using traditional cell references, they would use application domain concepts only.
The two Microsoft Excel add-ons have been tested to run in Microsoft Excel 2003, Microsoft Excel 2007 and Microsoft Excel 2010. They can be downloaded freely at http://cs.uef.fi/spreadsheet_tools/ together with installation instructions.
The doctoral dissertation of Master of Science Bennett Kankuzi entitled Deficiencies in Spreadsheets: A Mental Model Perspective will be examined at the Faculty of Science and Forestry. The opponent in the public examination will be Research Fellow Sari Kujala, Aalto yliopisto and the custos will be Professor Jorma Sajaniemi, University of Eastern Finland.