Abstract
In this chapter we develop a simple model of the traditional spreadsheet. We outline the structure and operational semantics of this useful tool, with particular emphasis on the notion of update. This allows us to distill a methodology and a number of guiding principles for the deductive spreadsheet.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Notes
- 1.
To be more precise, a programmer can implement new operators and make them available by means of add-on modules, something for which an end-user often lacks the know-how. There have also been recent proposals (e.g., [PBB03]) for operators definable directly by an end-user, which do not require programming skills, but no such extension is part of any commercial product as of yet.
- 2.
More precisely, Excel’s SUM takes up to 30 arguments, each of which can be a range. We ignore the difference and assume the number of arguments to be arbitrary.
- 3.
In our model, we implicitly lift any restriction on the number of cells in a spreadsheet. All commercial applications force spreadsheets to be finite: for example, Microsoft Excel has “IV” as its last column (for a total of up to 256), and 65,536 rows. Other commercial products have similar bounds.
- 4.
More precisely, although a warning is always issued, most commercial applications allow the advanced user to enable a restricted form of circular references: assume for example that cell A1 contains the value 600, A2 contains the formula \(= -A3 {\ast} 0.2\) and A3 contains \(= A1 + A2\). There is a circular dependency between A2 and A3. Cell A2 is initialized with the value 0, which gives 600 for A3. At this point, A2 is re-evaluated on the basis of this new value for A3, yielding − 120 which is used to refine the value of A3 to 480. The values of cells A2 and A3 eventually converge to − 100 and 500, respectively. The maximum number of iterations is user-defined (100 by default in Microsoft Excel) and the notion of convergence is taken to mean that a cell’s value is within another user-defined constant of the value in the previous iteration (the default is 0.001 in Excel). Note that this amounts to numerically solving the equation \(600 - 0.2x = x\).
While the model we are developing can easily accommodate such bounded iterations, we refrain from doing so for several reasons. First, this would add a layer of complication that would only have the effect of obscuring our deductive extensions. Second, the user base of this esoteric technology is very small. Indeed, few users are even aware of its existence and even fewer can competently handle it. Third, circular evaluation is disabled by default in all commercial products. Indeed, end-users have a hard time writing correct spreadsheets in the first place (see [Pan98] for some disturbing statistics) even without this error-prone option.
- 5.
A more accurate definition of ordinal power would have cases for all limit ordinals, not just ω. However, we will not need any ordinal bigger than ω thanks to our continuity hypothesis.
- 6.
This is the classical textbook on the theory of logic programming: it collects and unifies results on SLD-resolution, unification, model and fixpoint semantics, negation-as-failure, that had appeared in separate papers. It does not consider any of the recent proof-theoretic developments, but it is a valuable reference. As such, it has been used by generations of students and scholars of logic programming.
- 7.
This paper is an interesting account of the development of the theory of fixpoints.
- 8.
This very influential article carried out a systematic study of the number and type of errors found in spreadsheets. At the outset, the results are surprisingly high, which should limit our confidence about decisions made on the basis of spreadsheet calculations. This research also finds that this type and number is similar to programming in general, except that software engineering has developed methods to curb this error rate, while spreadsheet users take a more informal approach.
- 9.
This paper proposes a method for extending the traditional spreadsheet with user-defined functions without the expectation that an everyday user will acquire the sophistication of an addon programmer. This work relies on cognitive psychology techniques such as the cognitive dimension of notations and the attention investment model to retain the usability that characterizes the traditional spreadsheet. This work was extremely influential in the development of the deductive spreadsheet.
- 10.
This is the original article on the theory of fixpoints.
Annotated Bibliography
Lloyd, J. W. (1987). Foundations of logic programming (2nd extended ed.). Berlin/New York: Springer. Footnote
This is the classical textbook on the theory of logic programming: it collects and unifies results on SLD-resolution, unification, model and fixpoint semantics, negation-as-failure, that had appeared in separate papers. It does not consider any of the recent proof-theoretic developments, but it is a valuable reference. As such, it has been used by generations of students and scholars of logic programming.
Lassez, J. -L., Nguyen, V. L., & Sonenberg, L. (1982). Fixed point theorems and semantics: A folk tale. Information Processing Letters, 14(3), 112–116. Footnote
This paper is an interesting account of the development of the theory of fixpoints.
Panko, R. R. (1998). What we know about spreadsheet errors. Journal of End User Computing (Special issue on Scaling Up End User Development), 10(2), 15–21. Available at http://panko.shidler.hawaii.edu/ssr/Mypapers/whatknow.htm. Footnote
This very influential article carried out a systematic study of the number and type of errors found in spreadsheets. At the outset, the results are surprisingly high, which should limit our confidence about decisions made on the basis of spreadsheet calculations. This research also finds that this type and number is similar to programming in general, except that software engineering has developed methods to curb this error rate, while spreadsheet users take a more informal approach.
Peyton-Jones, S., Blackwell, A., & Burnett, M. (2003). A user-centred approach to functions in Excel. In Proceedings of the eighth ACM SIGPLAN international conference on functional programming, Uppsala (pp. 165–176). ACM. Footnote
This paper proposes a method for extending the traditional spreadsheet with user-defined functions without the expectation that an everyday user will acquire the sophistication of an addon programmer. This work relies on cognitive psychology techniques such as the cognitive dimension of notations and the attention investment model to retain the usability that characterizes the traditional spreadsheet. This work was extremely influential in the development of the deductive spreadsheet.
Tarski, A. (1955). A lattice-theoretical fixpoint theorem and its applications. Pacific Journal of Mathematics, 5, 255–309. Footnote
This is the original article on the theory of fixpoints.
Author information
Authors and Affiliations
Rights and permissions
Copyright information
© 2013 Springer-Verlag Berlin Heidelberg
About this chapter
Cite this chapter
Cervesato, I. (2013). The Traditional Spreadsheet. In: The Deductive Spreadsheet. Cognitive Technologies. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-642-37747-1_3
Download citation
DOI: https://doi.org/10.1007/978-3-642-37747-1_3
Published:
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-642-37746-4
Online ISBN: 978-3-642-37747-1
eBook Packages: Computer ScienceComputer Science (R0)