Authors
Bennett Kankuzi
Abstract
Most errors in spreadsheets are formula-based. Referenced cells in formulas are normally presented using the traditional A1 cell referencing style. A spreadsheet user has to therefore mentally map referenced cells to their corresponding labels in order to comprehend a formula in the context of the problem domain.
In this paper, we give a detailed description of an algorithm that can be used to dynamically translate traditional spreadsheet formulas to their problem domain equivalents which are easier to understand.
The translation is done as one accesses a formula cell in a spreadsheet. The formula translation is based on inferred labels of referenced cells in the formula. The aim of the translation is to ease the cognitive load on the spreadsheet user and hence improving the error-prone spreadsheet development process.
The paper also highlights some factors that need to be taken into consideration when dynamically translating spreadsheet formulas:
- The number of referenced cells in a formula and the distance of labels from referenced cells will determine the speed of the translation process hence affecting system responsiveness as one navigates through a spreadsheet.
- Unpredictable spatial arrangement of data in spreadsheets (spreadsheet layout) can also pose a challenge to the translation algorithm which may lead to mis-translation of spreadsheet formulas.
These challenges might increase the cognitive load on the spreadsheet user hence negating the purpose of dynamically translating spreadsheet formulas.
Sample
The formula in C9 is automatically translated to a problem domain narrative "SUM( Jan | James Bourne ... Jan | Jasmine Hunt )" and referenced cells C5, C6, C7 and C8 are highlighted.
The tool also marks a formula cell with a pink right border.
This paper has three key contributions:
- Detailed description of an algorithm that dynamically translates spreadsheet formulas to problem domain narratives.
- Demonstrated how the number of referenced cells and the distance to labels determines the speed of the translation process and consequently system responsiveness.
- Demonstrated how varying layouts pose a challenge and shown how to mitigate the impact of this challenge.
Publication
2017, 28th annual conference of Psychology of Programming Interest Group (PPIG), July
Full article
Dynamic translation of spreadsheet formulas to problem domain narratives
