Courses/Computer Science/CPSC 203/CPSC 203 2008Winter L03/CPSC 203 2008Winter L03 Lectures/Lecture 6

Lecture 6

Today we recap our Spreadsheet data model from last lecture, look at some more examples of spreadsheet functions, and review spreadsheet design principles.

• House Keeping
  • Review Course Lecture Schedule
  • Term Project Intro and Tutorial Schedule on BB (quickly view term project intro)
  • MIDTERM: Tuesday February 26th

• Todays Goals
  • Quick Recap of Spreadsheet Data Model
  • Introduction to Spreadsheet Design (Principles + a Practice Exercise).

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
   1. location (cell range),
   2. name,
   3. the Data Class it is (Raw Data, Statistical Summary, Calculated Variable, Score etc.),
   4. Data 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 "Design On Paper" Exercise

In this exercise you do a small "Design on Paper" based on some data on past US Presidential Elections. You have 20 minutes.

1. Break into your Tutorial Groups
2. Each group uses one page
3. Divide the page up into 6 sections from "Introduction .... to .... Presentation"
4. Design Each Section on paper.
5. Hand Design up to Front.

We will then use these ideas in the latter half of the class to take our "basic spreadsheet data" and properly design it so that it is well formatted and self documenting (the exercise will continue through next class).

Homework

Take the principles from this class, to do your own version of the Presidents Spreadsheet (basic data on BB).

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