Rick ChimeraRick Chimera

School of Computing and InformaticsSchool of Computing and InformaticsArizona State UniversityArizona State University

Tempe, Arizona USATempe, Arizona USA

SpreadsheetsSpreadsheets

CPI 101: Meeting 11CPI 101: Meeting 11

Why Learn about Spreadsheets? - AcademicWhy Learn about Spreadsheets? - Academic

Another way to do Modelling and InferenceAnother way to do Modelling and InferenceNot just mathematical modelling, we’ll see a room scheduling exampleNot just mathematical modelling, we’ll see a room scheduling example

Another example of an Interpreted languageAnother example of an Interpreted languageGood: immediate (Excel is “super immediate”)Good: immediate (Excel is “super immediate”)

Bad: less help with errorsBad: less help with errors

Usability due to appropriate metaphorUsability due to appropriate metaphorAccounting ledger gridAccounting ledger grid

““Killer app”! Without Excel, Microsoft would not be number 1Killer app”! Without Excel, Microsoft would not be number 1

*Constraint propagation*Constraint propagation

Why Learn about Spreadsheets? - PracticalWhy Learn about Spreadsheets? - Practical

PopularPopular program in today’s work environment program in today’s work environmentNot just for accountants and number-crunchingNot just for accountants and number-crunching

Timelines, resources, contacts, etc.Timelines, resources, contacts, etc.

Has formatting, interactive formsHas formatting, interactive forms

UsefulUseful program for the individual program for the individualCan keep track of concerts you attended (sort by date, band, venue, rating)Can keep track of concerts you attended (sort by date, band, venue, rating)

*Plan and then track your budget*Plan and then track your budget

Good way to Good way to organizeorganize repetitive or similar data repetitive or similar dataCan think of columns as defining an Can think of columns as defining an objectobject with one property per column with one property per column

Can think of rows as being separate Can think of rows as being separate instancesinstances of that object of that object

What is a Spreadsheet?What is a Spreadsheet?

Based on accounting ledger form (big grid)Based on accounting ledger form (big grid)Such a good idea, it grew to be betterSuch a good idea, it grew to be better

Columns, rows, cells, and cell rangesColumns, rows, cells, and cell rangesColumn A, Row 1, Cell A1, Cell Range A1:A8 (8 cells) A1:C3 (9 cells)Column A, Row 1, Cell A1, Cell Range A1:A8 (8 cells) A1:C3 (9 cells)

Calculates cell valuesCalculates cell values that depend on other cell values through that depend on other cell values through formulas and functionsformulas and functionsCell A4 is the value of cell A3 multiplied by .08Cell A4 is the value of cell A3 multiplied by .08

Chains of dependencies that propagate their effectsChains of dependencies that propagate their effectsUnidirectionalUnidirectional

1-to-many or many-to-1 relationships, but not many-to-many1-to-many or many-to-1 relationships, but not many-to-many

What is So Great about Spreadsheets?What is So Great about Spreadsheets?

Great way to deal with data Great way to deal with data dependenciesdependencies

Allows for “What If” analysisAllows for “What If” analysis

Interesting way to deal with Interesting way to deal with constraintsconstraintsWe’ll briefly discuss logical spreadsheets and constraint satisfactionWe’ll briefly discuss logical spreadsheets and constraint satisfaction

Import and export data to other programsImport and export data to other programsXMLXML

Comma Separated Values (csv)Comma Separated Values (csv)

also called Tab Delimited file, some allow any characteralso called Tab Delimited file, some allow any character

What is Not So Great about Spreadsheets?What is Not So Great about Spreadsheets?

““(From a programming language point of view, then,) (From a programming language point of view, then,) spreadsheets lack the most fundamental mechanism that we spreadsheets lack the most fundamental mechanism that we use to control complexity: the ability to define use to control complexity: the ability to define re-usablere-usable abstractionsabstractions.. In effect, they deny to end-user programmers the In effect, they deny to end-user programmers the most powerful weapon in our armory.”most powerful weapon in our armory.”- Simon Peyton Jones, Margaret Burnett and Alan Blackwell in their paper - Simon Peyton Jones, Margaret Burnett and Alan Blackwell in their paper Improving the world's most popular functional language: user-defined functions in ExcelImproving the world's most popular functional language: user-defined functions in Excel

Similar to javascript without functionsSimilar to javascript without functions

Could be done via copy and paste (more bad than good)Could be done via copy and paste (more bad than good)

What is a Logical Spreadsheet?What is a Logical Spreadsheet?

Supporting “many-to-many” relationships that are Supporting “many-to-many” relationships that are omnidirectionalomnidirectionalAs I like to think about it: “Mutual dependencies” with operational characteristics that As I like to think about it: “Mutual dependencies” with operational characteristics that avoid/leverage “circular references”avoid/leverage “circular references”

Meeting example:Meeting example:Start Time, End Time, and Event OwnerStart Time, End Time, and Event Owner

constraint: Start Time (S) and End Time (E) constraint: Start Time (S) and End Time (E) before( S, E ) before( S, E )

“ “The start time must be before the end time.”The start time must be before the end time.”

constraint: Event Owner (O) and ~Senior Manager(O) constraint: Event Owner (O) and ~Senior Manager(O) ~Event Room (301) ~Event Room (301)

“ “If the event owner is not a senior manager then room 301 cannot be reserved.”If the event owner is not a senior manager then room 301 cannot be reserved.”

from Michael Kassof, Stanford Logic Group, talk on Logical Spreadsheets August 1, 2006 (without permission)

Working with a SpreadsheetWorking with a Spreadsheet

Take advantage of Good InteractivityTake advantage of Good Interactivity

Interactive Help SystemInteractive Help System

Interactive Formula and Function SystemInteractive Formula and Function System

““Intelligent” Interactivity, especially when growing a Intelligent” Interactivity, especially when growing a spreadsheetspreadsheetCopy and Paste with Copy and Paste with relativerelative cell specifications cell specifications

Spreadsheets In Action, OOPS!Spreadsheets In Action, OOPS!

Lots of potential for cell values to be an ERRORLots of potential for cell values to be an ERROR

Divide by Zero (#DIV/0!)Divide by Zero (#DIV/0!)

Not a Number (#VALUE!)Not a Number (#VALUE!)

No such cell to reference (#REF!)No such cell to reference (#REF!)

Circular Reference (“Circular: A1” on status line)Circular Reference (“Circular: A1” on status line)When a formula eventually leads back to oneself, directly or indirectlyWhen a formula eventually leads back to oneself, directly or indirectly

Typically this is a thought error, but does not have to be an operational errorTypically this is a thought error, but does not have to be an operational error