Courses/Computer Science/CPSC 203/CPSC 203 2007Fall L04/CPSC 203 2007Fall L04 Lectures/Lecture 5

Lecture 5

We introduce spreadsheets as a flexible medium for playing with data, organizing data, analyzing data

The objectives of today's class are:

• House Keeping
  • Review Important Course Dates
  • Quick look at some more of the Term Project initial statements
  • How are the labs/tutorials going??

• Spreadsheets 001
  • Two course themes: "Information in the large"; "Information in the small"
  • New glossary on spreadsheets
  • Spreadsheet data model
  • Spreadsheet Examples
  • Spreadsheet design principles

Lecture Glossary

• Spreadsheet
• Data Model
• Operator
• Function
• Domain
• Range
• Case
• Variable
• Properties
• Value(s)

Spreadsheet Data Model

• cell X -----> cell Y
• Domain X -----> Range Y
• Dots -- the cells
• Edges -- Operators and Functions
• Properties of a Spreadsheet Cell:
  • Format (i.e. colour, shading, background, font-size)
  • Type (i.e. data type)

Spreadsheet Examples

We go through several examples of spreadsheets and critique them for what works, what does not work

Mini-Tutorial: Spreadsheet Design

Below are some general rules for building a well designed spreadsheet.

Some Basic Rules about Design

1. Design the spreadsheet on paper first. Graph paper often works well.
2. Test and edit your calculations. Where appropriate use intermediate calculations and check-sums to ensure calculations are correct.
3. Keep the components of a calculation visible. No "magic numbers". Place fixed numbers used in a calculation in their own cell with a descriptive title.
4. Be aware of the "space" or "geography" of the spreadsheet. Arrange your information so that it is well spaced and easy to take in at a glance.

Parts of a Well Designed Spreadsheet

These parts can be considered components of a disciplined approach to building spreadsheet so they are self-documenting. The parts could each be in their own sheet, or they could be in a single sheet.

1. Introduction -- What is this spreadsheet or workbook about. Note the title, purpose, author, creation and revision dates etc.
2. Model and Assumptions. Justify any models, summary statistics, or calculated variables you are using.
3. Data Dictionary. For every variable in the spreadsheet note: its location (cell range), name, the type of data it is (Raw Data, Statistical Summary, Calculated Variable, Score etc.), Field Format (e.g. Integer, Text, Currency, Date, etc.).
4. Raw Data. Present your raw data in tabular form -- with columns representing variables and rows representing cases.
5. Calculated Data.
   1. Summary Statistics --Usually Summary statistics result from calculations across rows for a single column.
   2. Derived variables are often based on calculations across columns for a row.
6. Presentation' (Reporting)
   1. Emphasize the final information you wish to show without excessive background details.
   2. Use charts wherever appropriate to summarize large volumes of data

Resources

The Elements of Spreadsheet Style. 1986. By J.M. Nevison (Great, cheap, book on spreadsheet design. Out of print, but easily ordered from Amazon).

A Brief History of Spreadsheets: http://dssresources.com/history/sshistory.html A nice little article on the origins of spreadsheets by some guys with too much facial hair.

Another overview on Spreadsheets is at: http://en.wikipedia.org/wiki/Spreadsheet