Dept. of Computer
Science
CSCI A111
A Survey of Computers and Computing
Fall 1998 - First 8 Weeks
Module 5 - Intermediate Spreadsheets
Back to the A111 Home Page
Learning Objectives
At the completion of this module, you will know:
- What functions are
- How to use functions
- Why named ranges make your spreadsheet easier to use
- The distinction between absolute and relative addressing
- How to create named ranges
- How to write formulas containing named ranges
- What data to sort
- How "keys" affect sorting order
- How to sort a portion of a spreadsheet
Guidelines
- Whenever possible, use named ranges - they increase the readability of your document.
- Never use a list of cells as an argument to a function when you can instead use a range.
- Always save before you sort.
- Always select exactly the data which you wish to sort.
Drills
Practice with Named Ranges
Create a simple budget for how you spend your time during the week. You should have a column for each of the seven days of the week, and a row for each different activity you perform. For each activity (and each day of the week), specify the number of hours you spend doing that activity (use decimals for fractions of hours). Your budget need not be very precise: you are free to choose broad activity categories such as Eating, Sleeping, Studying, Watching TV, Socializing, Transit, Reading The Better Way, etc.
Add a title to the spreadsheet, centered across multiple columns.
Create columns which compute the minimum, maximum, and average number of hours spent on each activity each day.
Name the ranges of cells specifying each day. Do not include the actual headings "Monday", "Tuesday", etc. Using these range names, add a row to track the total number of hours accounted for on each day. This row will appear beneath all of the rows for the individual activities. Then, use these named ranges to add a column to track the average, minimum, and maximum number of hours spent per day on each activity.
Format the cells to make the data more readable. Headings and summary rows and columns (such as average, min and max) are good candidates for emphasis. Consider using borders, shading, and colours to enhance the spreadsheet.
Finally, sort the activities using the average hours spent as the first key and the maximum hours spent as the second key.
Sorting Practice
Download the file "bombers.xls" from FirstClass. This spreadsheet contains the names of several World War II bombers (actual), the type of aircraft each was (actual), and the number of missions flown by each bomber (made-up). To gain some practice with sorting, try some of the following sorts:
- Sort the bombers (and all the data about them) in alphabetical order by name.
- Sort the bombers (and all the data about them) in order from highest to lowest number of missions flown, with ties being broken by listing the bombers in alphabetical order by name.
- Sort the bombers (and all the data about them) by type, alphabetically. Make the secondary key of this sort be the number of missions flown, in descending order. Make the third key be name, sorted in alphabetical order.