Transcript Tools for modeling and inference: Constraint reasoning (e.g., spreadsheets)

CPI 101: Meeting 11
Spreadsheets
Rick Chimera
School of Computing and Informatics
Arizona State University
Tempe, Arizona USA
Why Learn about Spreadsheets? - Academic
Another way to do Modelling and Inference

Not just mathematical modelling, we’ll see a room scheduling example
Another example of an Interpreted language

Good: immediate (Excel is “super immediate”)

Bad: less help with errors
Usability due to appropriate metaphor

Accounting ledger grid
“Killer app”! Without Excel, Microsoft would not be number 1
*Constraint propagation
Why Learn about Spreadsheets? - Practical
Popular program in today’s work environment

Not just for accountants and number-crunching

Timelines, resources, contacts, etc.

Has formatting, interactive forms
Useful program for the individual

Can keep track of concerts you attended (sort by date, band, venue, rating)

*Plan and then track your budget
Good way to organize repetitive or similar data

Can think of columns as defining an object with one property per column

Can think of rows as being separate instances of that object
What is a Spreadsheet?
Based on accounting ledger form (big grid)

Such a good idea, it grew to be better
Columns, rows, cells, and cell ranges

Column A, Row 1, Cell A1, Cell Range A1:A8 (8 cells)
A1:C3 (9 cells)
Calculates cell values that depend on other cell values through
formulas and functions

Cell A4 is the value of cell A3 multiplied by .08
Chains of dependencies that propagate their effects

Unidirectional

1-to-many or many-to-1 relationships, but not many-to-many
What is So Great about Spreadsheets?
Great way to deal with data dependencies
Allows for “What If” analysis
Interesting way to deal with constraints

We’ll briefly discuss logical spreadsheets and constraint satisfaction
Import and export data to other programs

XML

Comma Separated Values (csv)

also called Tab Delimited file, some allow any character
What is Not So Great about Spreadsheets?
“(From a programming language point of view, then,)
spreadsheets lack the most fundamental mechanism that we
use to control complexity: the ability to define re-usable
abstractions. In effect, they deny to end-user programmers the
most powerful weapon in our armory.”
- Simon Peyton Jones, Margaret Burnett and Alan Blackwell in their paper Improving the world's most
popular functional language: user-defined functions in Excel
Similar to javascript without functions
Could be done via copy and paste (more bad than good)
What is a Logical Spreadsheet?
Supporting “many-to-many” relationships that are
omnidirectional
As I like to think about it: “Mutual dependencies” with operational characteristics that
avoid/leverage “circular references”

Meeting example:

Start Time, End Time, and Event Owner

constraint: Start Time (S) and End Time (E)  before( S, E )
“The start time must be before the end time.”

constraint: Event Owner (O) and ~Senior Manager(O)  ~Event Room (301)
“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 Spreadsheet
Take advantage of Good Interactivity
Interactive Help System
Interactive Formula and Function System
“Intelligent” Interactivity, especially when growing a
spreadsheet

Copy and Paste with relative cell specifications
Spreadsheets In Action, OOPS!
Lots of potential for cell values to be an ERROR
Divide by Zero (#DIV/0!)
Not a Number (#VALUE!)
No such cell to reference (#REF!)
Circular Reference (“Circular: A1” on status line)

When a formula eventually leads back to oneself, directly or indirectly

Typically this is a thought error, but does not have to be an operational error