A summary of my PhD thesis in not so technical language

My PhD thesis defence was held on 28th August 2015 at 12 noon at the University of Eastern Finland School of Computing in the Louhela Auditorium, Joensuu Science Park in Joensuu, Finland. The opponent was Research Fellow Sari Kujala and the custos was Professor Jorma Sajaniemi. A press release of my doctoral thesis to the Finnish media (as required by Finnish academic tradition) is at https://www.uef.fi/en/-/28-8-virheita-taulukkolaskennassa-onko-aika-muuttaa-taulukkopohjien-tekotapoja- 

The title of my PhD thesis is "Deficiencies in Spreadsheets: A Mental Model Perspective". The thesis can be downloaded at http://epublications.uef.fi/pub/urn_isbn_978-952-61-1828-4/index_en.html  Below is a summary of the thesis in not so technical language: 

Millions of people, globally, use spreadsheets in their daily work. For example, information extracted from spreadsheets is used in decision making and planning in various businesses and organizations. Spreadsheet software is ubiquitous such that almost all desktop computers have some spreadsheet software installed on them. Their easy to use interface, coupled with their powerful data manipulation capabilities, has made spreadsheets a darling to many end-users. Despite this popularity of spreadsheets, different researchers have shown that most spreadsheets have errors. Unfortunately, these errors have proven to be not trivial. For example, it is documented how some companies have lost millions of dollars due to errors in the spreadsheets they use. Research, however, has also shown that most spreadsheet errors involve construction and use of formulae.

This thesis tackled this problem of errors by studying mental models of spreadsheet creators performing various spreadsheet activities. In other words, we opted to study what spreadsheet creators think when they are doing various spreadsheet activities. We opted for this approach because it is a common assertion that humans have mental models of the systems they interact with, and as a result, it is difficult to explain many aspects of human behaviour when interacting with systems, without resorting to a construct such as mental models.

Using a constructive research approach, we conducted three empirical studies and also developed two Microsoft Excel add-ons. Spreadsheet creators are normally not professional programmers but are experts in their application domains such as accounting. However, the results of the studies showed 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, we created a Microsoft Excel add-on that translates traditional spreadsheet formulae into application domain concepts so that spreadsheet creators should map easily application domain concepts to their spreadsheet-specific counterparts. The add-on also automatically highlights all cells which are referenced in a translated spreadsheet formula. The add-on can be used for different purposes such as being used as a verification aid when creating a spreadsheet, as a comprehension aid when trying to understand a spreadsheet created by others, and as a debugging aid for locating potential errors in a spreadsheet.

The add-on was evaluated and found to be easy to learn and also helped spreadsheet creators to locate more errors in their spreadsheets. The add-on was also noted to help 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 experts in their application domains and are not necessarily professional programmers and as such they are more familiar with application domain concepts rather than low-level spreadsheet specific concepts.

We also developed another Microsoft Excel add-on (a prototype one) by extending the previously described Microsoft Excel add-on 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 has the implication that we can have a spreadsheet paradigm in which spreadsheet creators develop their spreadsheets using application domain concepts only rather than labouring themselves to learn to use traditional cell references. In other words, 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, of which they are already familiar of.

The two 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.