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

Lecture 6

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

The objectives of today's class are:

• House Keeping
  • What do you need to know for lab quiz next week
  • Assignment 1: You can use Excel, Open Office Spreadsheet, or Google Spreadsheet & Docs. BUT -- must save the file as .xls format

• Spreadsheets 001
  • Spreadsheet Interface versus Spreadsheet Model
  • Expanded glossary on spreadsheets and data model review
  • Spreadsheet Design Principles Overview -- Adding the design principles to a basic spreadsheet
  • Spreadsheet Design Principles -- 3 examples of good design.
  • Spreadsheet design principles

Lecture Glossary (Expanded Slightly from Lecture 5)

• Spreadsheet: Row by column array of cells. Values are passed from cell to cell.
• Cell: "Atomic" unit of spreadsheet. In a cell there is data (values), formats (how cell looks) + data types (what kind of value: integer, text...etc)
• Data Model: A model of relationships between pieces of data.
• Operator & Function: Works on values to transform them.(i.e. +, -, X...)
• Domain: Cells from which values are taken.
• Range: Cells to which data is given.
• Case: A unit of observation
• Variable: An observed attribute
• Properties
• Value(s)
• Relative and Absolute Reference ("Location")

Spreadsheet Data Model (Reviewed Last Class)

• 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)

A Spreadsheet is a "lattice" or "grid" of cells. Functions and operators allow values to be moved from cell to cell. A cell can not write back into itself. Essentially a spreadsheet is a very user friendly programming environment.

Mini-Tutorial: Spreadsheet Design (introduced end of last class)

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
   1. location (cell range),
   2. name,
   3. the Data Class it is (Raw Data, Statistical Summary, Calculated Variable, Score etc.),
   4. Field Type (e.g. Integer, Text, Currency, Date, etc.) and
   5. Description (a description of the data or what it's 'purpose' is).
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

Well Designed Spreadsheet Examples

1. We take a basic spreadsheet, and sketch out some of the additions we can make to it for good design.
2. We look at 3 examples of good design from Last Semester's Assignment 1, and critique what works and what does not.

Homework

Look up the basic functions and formats for:

• Statistical Data Summarization: Summation, Mean, Standard Deviation, Mode, Median, Variance.
• How "If Then" functions work.
• How "Lookup" works.
• Can you figure out the function to combine two pieces of text????

This sets us up to shift next week into Data Analysis using spreadsheets, and introducing Assignment 1

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