Courses/Computer Science/CPSC 203/CPSC 203 Template/Labs Template/Week 2 - Lab 2: Spreadsheet Design Rules
Contents
Introduction
Today's tutorial continues to build on the visual elements to spreadsheet design, introduced in the last tutorial.
Spreadsheet Design Rules
- Some basic rules about design
- Parts of a well designed spreadsheet
Some Basic Rules about Design
- Design the spreadsheet on paper first. Graph paper often works well.
- Test and edit your calculations. Where appropriate use intermediate calculations and check-sums to ensure calculations are correct.
- 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.
- 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
Adapted from: The Elements of Spreadsheet Style. 1986. By J.M. Nevison
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.
- Introduction -- What is this spreadsheet or workbook about. Note the title, purpose, author, creation and revision dates etc.
- Model and Assumptions -- Justify any models, summary statistics, or calculated variables you are using.
- Data Dictionary -- For every variable in the spreadsheet note: its
- Location (cell range),
- Name,
- The Data Class it is (Raw Data, Statistical Summary, Calculated Variable, Score etc.),
- Data Type (e.g. Integer, Text, Currency, Date, etc.) and
- Description (a description of the data or what it's 'purpose' is).
- Raw Data -- Present your raw data in tabular form -- with columns representing variables and rows representing cases.
- Calculated Data
- Summary Statistics -- Usually Summary statistics result from calculations across rows for a single column.
- Derived variables are often based on calculations across columns for a row.
- Presentation (Reporting)
- Emphasize the final information you wish to show without excessive background details.
- Use charts wherever appropriate to summarize large volumes of data.
Example
The following example shows incremental improvements, working towards good spreadsheet design.
Part 1
In this first iteration, two separate sheets are used:
- data
- presentation
Media:SpShtLec3_PresidentsDataAndAnalysis_1_20080205.xls
Part 2
In this second iteration, five separate sheets are used:
- introduction
- model and assumptions
- data dictionary
- data
- presentation
Media:SpShtLec3 PresidentsDataAndAnalysis 2 20080205.xls
Part 3
In this third iteration, the same five sheets are used (as in Part 2), with further refinements, descriptions, and presentation of the two models used.
Media:AnalysisExamples PresidentsDataAndAnalysis 4 20080306.xls