Calculation View: multiple-representation editing in spreadsheets

Abstract

Spreadsheet errors are ubiquitous and costly, an unfortunate combination that is well-reported. A large class of these errors can be attributed to the inability to clearly see the underlying computational structure, as well as poor support for abstraction (encapsulation, re-use, etc). In this paper we propose a novel solution: a multiple-representation spreadsheet containing additional representations that allow abstract operations, without altering the conventional grid representation or its formula syntax. Through a user study, we demonstrate that the use of multiple representations can significantly improve user performance when performing spreadsheet authoring and debugging tasks. We close with a discussion of design implications and outline future directions for this line of inquiry.

Elastic sheet-defined functions: Generalising spreadsheet functions to variable-size input arrays

“…We therefore borrow a recently introduced textual notation for describing the data and computations of a spreadsheet grid -the Calculation View (Sarkar et al, 2018) of the spreadsheet. The complete Calculation View language as used in this paper is given in Figure 2 below for reference.…”

“…• Sarkar et al (2018) propose a simple textual notation for spreadsheet programs (Section 3). We develop this idea further by introducing new notation for SDFs.…”

XLBlocks: a Block-based Formula Editor for Spreadsheet Formulas

“…Most related to our research is the work of Burnet et al [29], Leitão and Roast [30], Sarkar et al [31], and Abraham et al [21].…”

End-User Probabilistic Programming

“…E1 = "Budget for Jan 2018" E3 = "Income"; F4 = 2000 E5 = "Expenses" E6 = "Rent"; F6 = 1100 // A certain cost E7 = "Commute"; F7 = 85 // Another certain cost E8 = "Sofa! "; F8 = 700 // Clara is deciding whether to buy this item E9 = "Utilities"; F9 = 100 // But she is uncertain about her utilities bill E10 = "Total expenses"; F10 = SUM(F6:F9) // value: 1985 E12 = "Balance"; F12 = F4−F10 // value: 15 (Instead of the standard view of the spreadsheet, which shows the values of formulas but not the formulas themselves, we use a textual notation for spreadsheets known as Calculation View [29], and show values of formulas in comments.) Clara lists out her certain costs, rent and commute, the cost of the sofa, and puts in her estimate of the utilities bill.…”

“…function BUDGET( F9 ) returns F12:G12 { E1 = "Budget for Jan 2018" E3 = "Income"; F4 = 2000 E5 = "Expenses" E6 = "Rent"; F6 = 1100 // A certain cost E7 = "Commute"; F7 = 85 // Another certain cost E8 = "Sofa! "; F8 = 700 // Clara is deciding whether to buy this item E9 = "Utilities"; F9 = 100 // This cell is the parameter to the SDF E10 = "Total expenses"; F10 = SUM(F6:F9) E12 = "Balance"; F12 = F4−F10; G12 = IF(F12<0,1,0) // Result cells } (Here we rely on an extension [22] of Calculation View [29] for a textual notation for sheet-defined functions. Instead, an implementation of sheet-defined functions would provide a graphical interface for the user to specify the function name, parameters, and other metadata.…”