Spreadsheets are Code: An Overview of Software Engineering Approaches Applied to Spreadsheets

Hermans, Felienne; Jansen, Bas; Roy, Sohon; Aivaloglou, Efthimia; Swidan, Alaaeddin; Hoepelman, David

doi:10.1109/saner.2016.86

Cited by 24 publications

(28 citation statements)

References 36 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…Formulas may consist of complex, nested expressions, including conditionals and other forms of control flow. For these and other reasons, spreadsheets can be viewed as code [16], and spreadsheet users are canonical examples of end-user programmers [18]: people who write code primarily for their own use. Even though they write code, end-user programmers are usually not professional developers.…”

Section: Background: Spreadsheets and End-user Programmingmentioning

confidence: 99%

End-User Probabilistic Programming

Borghouts

Gordon

Sarkar

et al. 2019

Quantitative Evaluation of Systems

View full text Add to dashboard Cite

Probabilistic programming aims to help users make decisions under uncertainty. The user writes code representing a probabilistic model, and receives outcomes as distributions or summary statistics. We consider probabilistic programming for end-users, in particular spreadsheet users, estimated to number in tens to hundreds of millions. We examine the sources of uncertainty actually encountered by spreadsheet users, and their coping mechanisms, via an interview study. We examine spreadsheet-based interfaces and technology to help reason under uncertainty, via probabilistic and other means. We show how uncertain values can propagate uncertainty through spreadsheets, and how sheet-defined functions can be applied to handle uncertainty. Hence, we draw conclusions about the promise and limitations of probabilistic programming for end-users.

show abstract

Section: Background: Spreadsheets and End-user Programmingmentioning

confidence: 99%

End-User Probabilistic Programming

Borghouts

Gordon

Sarkar

et al. 2019

Quantitative Evaluation of Systems

View full text Add to dashboard Cite

show abstract

“…Inspired by this observation, we propose our AST-based approach to automatically accomplishing such kind of formula refactorings. 6 e authors manually checked the nested-IF formulae in 100 randomly-sampled spreadsheets. 7 A dead branch will never be executed.…”

Section: Preliminary Studymentioning

confidence: 99%

“…Nested-IF expressions happen when end users write an IF function inside another IF or nested-IF function. According to previous research [3][4][5][6][7], nested-IF formulae in speadsheets are complex, unreadable, errorprone, as well as hard to debug and maintain. ere are also a lot of online discussions about the harm of nested-IF formulae.…”

Section: Introductionmentioning

confidence: 99%

Automated refactoring of nested-IF formulae in spreadsheets

Zhang

Han

Hao

et al. 2018

Proceedings of the 2018 26th ACM Joint Meeting on European Software Engineering Conference and Symposium on the Foundations of

View full text Add to dashboard Cite

Spreadsheets are the most popular end-user programming so ware, where formulae act like programs and also have smells. One well recognized common smell of spreadsheet formulae is nested-IF expressions, which have low readability and high cognitive cost for users, and are error-prone during reuse or maintenance. However, end users usually lack essential programming language knowledge and skills to tackle or even realize the problem. e previous research work has made very initial a empts in this aspect, while no e ective and automated approach is currently available.is paper rstly proposes an AST-based automated approach to systematically refactoring nested-IF formulae. e general idea is two-fold. First, we detect and remove logic redundancy on the AST. Second, we identify higher-level semantics that have been fragmented and sca ered, and reassemble the syntax using concise built-in functions. A comprehensive evaluation has been conducted against a real-world spreadsheet corpus, which is collected in a leading IT company for research purpose. e results with over 68,000 spreadsheets with 27 million nested-IF formulae reveal that our approach is able to relieve the smell of over 99% of nested-IF formulae. Over 50% of the refactorings have reduced nesting levels of the nested-IFs by more than a half. In addition, a survey involving 49 participants indicates that for most cases the participants prefer the refactored formulae, and agree on that such automated refactoring approach is necessary and helpful. 2

show abstract

“…It is estimated that the number of spreadsheet programmers is bigger than that of software programmers. ()…”

Section: Introductionmentioning

confidence: 99%

“…3 It is estimated that the number of spreadsheet programmers is bigger than that of software programmers. 4,5 Because of their widespread use, spreadsheets have been the topic of research since the 90s. 6 Recent research has often focused on analyzing and visualizing spreadsheets.…”

mentioning

confidence: 99%

Parsing Excel formulas: A grammar and its application on 4 large datasets

Aivaloglou

Hoepelman

Hermans

2017

J Software Evolu Process

Self Cite

View full text Add to dashboard Cite

Spreadsheets are popular end user programming tools, especially in the industrial world. This makes them interesting research targets. However, there does not exist a reliable grammar that is concise enough to facilitate formula parsing and analysis and to support research on spreadsheet codebases. This paper presents a grammar for spreadsheet formulas that can successfully parse 99.99% of more than 8 million unique formulas extracted from 4 spreadsheet datasets. Our grammar is compatible with the spreadsheet formula language, recognizes the spreadsheet formula elements that are required for supporting spreadsheets research, and produces parse trees aimed at further manipulation and analysis. Additionally, we use the grammar to analyze the characteristics of the formulas of the 4 datasets in 3 different dimensions: complexity, functionality, and data utilization. Our results show that (1) most Excel formulas are simple, however formulas with more than 50 functions or operations exist, (2) almost all formulas use data from other cells, which is often not local, and (3) a surprising number of referring mechanisms are used by less than 1% of the formulas.

show abstract

Spreadsheets are Code: An Overview of Software Engineering Approaches Applied to Spreadsheets

Cited by 24 publications

References 36 publications

End-User Probabilistic Programming

End-User Probabilistic Programming

Automated refactoring of nested-IF formulae in spreadsheets

Parsing Excel formulas: A grammar and its application on 4 large datasets

Contact Info

Product

Resources

About