Computational Models of Spreadsheet Development Basis for Educational

Computational Models of Spreadsheet Development Basis for Educational Approaches Karin Hodnigg, Markus Clermont, Roland T. Mittermeir University of Limerick, University of Klagenfurt Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 1

Motivation To raise spreadsheet quality, spreadsheet education has to start from the right paradigms. Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 2

Outline Conventional Programming Paradigm and Spreadsheet Programming Characteristics Implementing the Spreadsheet Paradigm Spreadsheet Semantics (Project-Screen-Paradigm) Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 3

Conventional Programming Paradigm and Spreadsheet Programming Characteristics Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 4

The Spreadsheet Paradigm • Spreadsheets ≠ conventional program • Data dependencies – Data Flow Program (? ) – Graph Reduction Problem (? ) A 1 A 2 B 2 A 3 B 4 Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 5

Data Flow Semantics Spreadsheet Semantics A 1 1 A 1 c 1 100 B 2 100 10 A 2 10 Eu. Sp. RIG 2004 0. 2 B 4 1 10 A 2 100 A 3 100 B 2 100 c A 3 100 c Computational Models of Spreadsheet Development B 4 6

Graph Reduction Semantics 1 1 A 1 10 A 2 Spreadsheet Semantics A 1 100 B 2 100 A 3 10 A 2 0. 2 B 4 100 B 2 A 3 100 B 4 0. 2 Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 7

The Whole Picture • Local Evaluation (of a cell‘s value) – graph reduction (DDG), possibly lazy • Global Evaluation (changes in a cell) – change tokens (reverse DDG), eager • Scope – Conventional scope hierarchy • local and global (classes/blocks) – Spreadsheet’s scope • complete sheet • portion of sheet (possibly) affected by change Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 8

Implementing the Spreadsheet Paradigm Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 9

Evaluation Strategies - Excel© value recalculation and change - Propagation (Formula Evaluator): list - Gnumeric – Distinct data structures for • single dependencies (=A 1) • range dependencies (=SUM(A 1: A 5)) – Every (transitively) dependent cell in a list (“evaluation queue”) – Recursive traversing of the given expression tree Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 10

Circular References • No explicit control flow no loop concept • Microsoft Excel© – Ignorable warning, cell provides 0 -value – “Iteration incorporation”: control panel • cell re-evaluation after any change • Gnumeric – Calculated without any warning – Re-evaluation on change in the transitive closure – Some functions support circular references Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 11

Development Process Specialties • Automated fill of cells (click and drag) – Automated generation of formulas / constants • Drag-and-Drop • Copy-and-Paste Adaptation of parameters – of formulas – of references Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 12

Copy/Paste and movement heuristics • Copy-and-Paste of cells ≠ Moving cells range references > distinct cell references > geometrical pattern Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 13

Spreadsheet Semantics and the Projection-Screen-Model Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 14

Relationships and Visibility • Cell level functional paradigm – Computations in cells are mainly covered. • Global “picture” ? – proposed model has to meet the basis. • Visibility and Scope – (Transitively) dependant cells – Implicit visibility of cells • Source cell does not “see” the target cell. • Cell “sees” all referenced cells. Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 15

The Projection Screen Model Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 16

The Projection Screen Model with Aggregation Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 17

Patterns resulting from development process • • Many-handed figures Queue on a Staircase Flying Carpets Recursive Images Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 18

Patterns: Many-handed figures Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 19

Patterns: Queue on a staircase Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 20

Patterns: Flying Carpets Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 21

Patterns: Recursive Images Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 22

Summary • Spreadsheet System Implementations are in conflict – with established paradigms – with each other • No established conceptual model – global vs. local viewpoint Proper training to raise spreadsheet quality needed! - “Project-Screen”-Model Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 23

“The limits of my language means the limits of my world. ” – Ludwig Wittgenstein – Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 24

Thank you Eu. Sp. RIG 2004 Computational Models of Spreadsheet Development 25