Detecting problematic lookup functions in spreadsheets

Hermans, Felienne; Aivaloglou, Efthimia; Jansen, Bas

doi:10.1109/vlhcc.2015.7357210

Cited by 6 publications

(2 citation statements)

References 9 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…The intersection of spreadsheets and software engineering is an active research area. Of particular note, in relation to this paper is research in refactoring for spreadsheets [38]- [41]. Gradual Structuring would benefit greatly if combined with refactoring.…”

Section: Related Workmentioning

confidence: 98%

Gradual structuring: Evolving the spreadsheet paradigm for expressiveness and learnability

Miller

Hermans

Braun

2016

2016 15th International Conference on Information Technology Based Higher Education and Training (ITHET)

Self Cite

View full text Add to dashboard Cite

Spreadsheets are arguably the most used form of programming and are frequently used in higher education to teach fundamental concepts about computation. Their success has shown that they are simple enough for a huge number of end users to learn and use. This is in contrast to traditional programming languages and the high dropout rate from introductory programming and computer science. However in comparison to traditional programming languages and structured modelling, spreadsheets are not expressive, placing a limit on the levels of computational thinking that can be taught using the spreadsheet paradigm. This limitation is imposed by the lack of programming language features and abstractions in the paradigm. Furthermore, more advanced spreadsheet features (e.g. array formulae, lookup formulae, R1C1 syntax) can be difficult to learn and use. This paper discusses the idea of adding language features to spreadsheets, enabling the gradual structuring of free-form spreadsheets to more structured models. We propose that this concept is termed Gradual Structuring, and is analogous to the programming language concept of gradual typing. In this analogy, spreadsheets take the place of dynamic programming and structured modelling of static programming. In programming languages, gradual typing allows dynamic programming to be mixed with static programming. It is our contention that dynamic programming is more learnable while static programming is more expressive and abstract. Gradual typing could be used to mitigate the issues in the teaching of traditional programming. Likewise Gradual Structuring can mitigate the conceptual limits that can be taught using current spreadsheets. The key language feature required to enable Gradual Structuring is the ability to logically group cells together so that a single formula can be applied to the grouped cells. This concept, termed cell grouping diminishes and can even eliminate the need for the ubiquitous and error-prone use of copy-pasted in spreadsheets. Moreover, it makes the structure present in spreadsheet models explicit. Cell grouping requires a cascade of other new languages features. Namely a more expressive referencing style, which in turned requires enabling labels to be moved to the row and column headers, and the hierarchical structuring of these headers. Respectively these language features are termed enhanced referencing and semantic axes. The ongoing research focusses on the usability and learnability of these language features. Spreadsheet applications exist that contain aspects of the features mentioned. However these applications do not enable Gradual Structuring and have taken a mainly technical, not human behavioural, approach to evolving the spreadsheet.

show abstract

Section: Related Workmentioning

confidence: 98%

Gradual structuring: Evolving the spreadsheet paradigm for expressiveness and learnability

Miller

Hermans

Braun

2016

2016 15th International Conference on Information Technology Based Higher Education and Training (ITHET)

Self Cite

View full text Add to dashboard Cite

show abstract

“…Excel has five functions that can be used to lookup data (similar to join functions in databases): VLOOKUP, HLOOKUP, INDEX, MATCH, and LOOKUP. They differ in syntax and arguments, and some have problematic defaults [15].…”

Section: Lookupsmentioning

confidence: 99%

XLBlocks: a Block-based Formula Editor for Spreadsheet Formulas

Jansen

Hermans

2019

2019 IEEE Symposium on Visual Languages and Human-Centric Computing (VL/HCC)

Self Cite

View full text Add to dashboard Cite

Spreadsheets are frequently used in industry to support critical business decisions. Unfortunately, they also suffer from error-proneness, which sometimes results in costly consequences. Experiments in the field of program education have shown that programmers tend to make fewer errors and can better focus on the logic of a program if they use a block-based language instead of a textual one. We hypothesize that a blockbased formula editor could support spreadsheet users in a similar way. Therefore, we develop XLBlocks and conduct a thinkaloud study with 13 experienced spreadsheet users from industry. Participants are asked to create and edit several formulas, using our block-based language. We then ask them to evaluate this editor using the Cognitive Dimensions of Notations framework. We found that for all dimensions the block-based formula editor received a better evaluation than the default text-based formula editor.

show abstract

Spreadsheet quality assurance: a literature review

Poon,

Lau,

et al. 2024

Front. Comput. Sci.

View full text Add to dashboard Cite

Spreadsheets are very common for information processing to support decision making by both professional developers and non-technical end users. Moreover, business intelligence and artificial intelligence are increasingly popular in the industry nowadays, where spreadsheets have been used as, or integrated into, intelligent or expert systems in various application domains. However, it has been repeatedly reported that faults often exist in operational spreadsheets, which could severely compromise the quality of conclusions and decisions based on the spreadsheets. With a view to systematically examining this problem via survey of existing work, we have conducted a comprehensive literature review on the quality issues and related techniques of spreadsheets over a 35.5-year period (from January 1987 to June 2022) for target journals and a 10.5-year period (from January 2012 to June 2022) for target conferences. Among other findings, two major ones are: (a) Spreadsheet quality is best addressed throughout the whole spreadsheet life cycle, rather than just focusing on a few specific stages of the life cycle. (b) Relatively more studies focus on spreadsheet testing and debugging (related to fault detection and removal) when compared with spreadsheet specification, modeling, and design (related to development). As prevention is better than cure, more research should be performed on the early stages of the spreadsheet life cycle. Enlightened by our comprehensive review, we have identified the major research gaps as well as highlighted key research directions for future work in the area.

show abstract

Detecting problematic lookup functions in spreadsheets

Cited by 6 publications

References 9 publications

Gradual structuring: Evolving the spreadsheet paradigm for expressiveness and learnability

Gradual structuring: Evolving the spreadsheet paradigm for expressiveness and learnability

XLBlocks: a Block-based Formula Editor for Spreadsheet Formulas

Spreadsheet quality assurance: a literature review

Contact Info

Product

Resources

About