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
 * 4) location (cell range),
 * 5) name,
 * 6) the Data Class it is (Raw Data, Statistical Summary, Calculated Variable, Score etc.),
 * 7) Field Type (e.g. Integer, Text, Currency, Date, etc.) and
 * 8) Description (a description of the data or what it's 'purpose' is).
 * 9) Raw Data. Present your raw data in tabular form -- with columns representing variables and rows representing cases.
 * 10) Calculated Data.
 * 11) Summary Statistics --Usually Summary statistics result from calculations across rows for a single column.
 * 12) Derived variables are often based on calculations across columns for a row.
 * 13) Presentation' (Reporting)
 * 14) Emphasize the final information you wish to show without excessive background details.
 * 15) 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