The Traditional Spreadsheet
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 gui
- PDF / 956,935 Bytes
- 22 Pages / 439.36 x 666.15 pts Page_size
- 40 Downloads / 210 Views
The Traditional Spreadsheet
This chapter presents a simple mathematical model of the traditional spreadsheet. While nothing deductive is to be found here, it will give us a solid foundation on which to develop a series of deductive extensions in the remaining chapters of Part I. This model will be a constant yardstick on which to gauge our proposals. It will highlight the important facets of a spreadsheet, from a functional point of view, thereby providing us with a development methodology and a number of guiding principles for the deductive spreadsheet. In Sect. 3.1, we define our abstraction of a spreadsheet. In Sect. 3.2, we characterize the evaluation process that replaces formulas with their calculated value. In Sect. 3.3, we look at updates and how they are propagated throughout a spreadsheet. In Sect. 3.4, we discuss the explanation mechanisms supported by a generic spreadsheet and comment on what is provided in commercial products. Section 3.5 summarizes this chapter.
3.1 Spreadsheet Model In this section, we give a simple mathematical model of the traditional spreadsheet and its constituent entities. Commercial products add many extra features, but their fundamental mode of operation does not stray far from this basic abstraction. As we develop our model, it will be useful to refer to the simple example spreadsheet in Fig. 3.1. The top part of the figure shows the contents of each cell as entered by the user: in particular, it displays the formulas of calculated cells rather than their result. We use the syntax commonly available in applications such as Microsoft Excel. The bottom part shows the spreadsheet as it appears on the user’s screen, after all the formulas have been evaluated. This typical spreadsheet records the hours worked weekly by a part-time employee and performs some simple statistics and accounting. Row 1 provides captions for each column. Rows 5 and greater are blank. The first two columns display the beginning and end date of each work week. The value in cell A2 is an actual date (the date of hire, I. Cervesato, The Deductive Spreadsheet, Cognitive Technologies, DOI 10.1007/978-3-642-37747-1 3, © Springer-Verlag Berlin Heidelberg 2013
33
34
1 2 3 4 5
3 The Traditional Spreadsheet A Monday 11/29/04 =A2+7 =A3 +7
B Friday =A2 +4 =A3 +4 =A4 +4
C Hours 8 17 25
D To date =C2 =D2 +C3 =D3 +C4
E Weekly average =D2/((A2 -A2)/7 + 1) =D3/((A3 -A2)/7 + 1) =D4/((A4-A2)/7 + 1)
(Entered spreadsheet)
1 2 3 4 5
A Monday 11/29/04 12/06/04 12/13/04
B Friday 12/03/04 12/10/04 12/17/04
C Hours 8 17 25
D To date 8 25 50
E Weekly average 8.00 12.50 16.67
(Evaluated spreadsheet)
Fig. 3.1 A simple timecard spreadsheet
maybe), while the remaining values are given by the shown formulas. Column C records the hours worked each week. Column D contains formulas to display the cumulative hours worked by the employee. Finally, column E shows the average number of hours worked weekly. Like many spreadsheets, this example is expected to grow with time, typically by adding one row each week until employ
Data Loading...
