Section3.0

# Section3.0 - Module 3 Introduction Having learned the...

This preview shows pages 1–4. Sign up to view the full content.

Module 3 Introduction Having learned the basics of spreadsheets, it is time to expand your knowledge and familiarity with this application software. More extensive familiarity with VLOOKUP, working with large amounts of data, using multiple worksheets and including data charts completes our introduction. The goals of the second spreadsheet unit are: to learn how to use VLOOKUP for searching in breakpoint tables; to learn how to manipulate worksheets with voluminous data and to work with multiple spreadsheets; to become familiar with chart and graph terminology and basics; to introduce the creation of charts and graphs from worksheet data. More specifically, by the end of the module, you will be able to: use VLOOKUP with inexact, as well as exact, matching; split and freeze worksheet windows; use multi-worksheet workbooks; create and modify charts created from spreadsheet data. Finally, additional exercises will provide you with further practice and also lead you to learning additional spreadsheet application features such as how to split text data into columns and how to print spreadsheets.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
CS 100 Module 3 3.2 3.0 Improving Spreadsheets © 2009, University of Waterloo You now have a good grounding in using spreadsheets, but there are many more features available, some of which are introduced in this section of the course. You are encouraged to explore other capabilities on your own.
CS 100 Module 3 3.3 3.1 Breakpoint Handling with VLOOKUP Recall that VLOOKUP takes four parameters as follows: 1) value or cell to match in the lookup table (the key) 2) table to search for a match (the key is matched against the leftmost column) 3) in which column to find the value to be returned (the leftmost column, containing the key, is column 1) 4) whether the greatest value less than or equal to the key is to be matched or an exact match is required When the fourth pa rameter is TRUE (or it is omitted altogether), “Price is Right” 1 matching is used: the values in the leftmost column of the table (the “bids”) are compared against the search key (the “price”) to find the row containing the greatest value not exceeding the search key. For example, if the search key is 5, then the row containing the greatest value not exceeding 5 is identified. Similarly, labels can be looked up in a table of text values based on alphabetical rather than numeric order. To use lookup with inexact matching, the table must be sorted in increasing order of the value in its leftmost column. Inexact matching with VLOOKUP should be used whenever a table reflects ranges of values or a sequence of breakpoints. Assume, for example, that a shop offers discount rates for bulk purchases as follows: # of Items Discount Rate 0-4 0% 5-9 4% 10-19 8% 20 or more 15% Such a table is entered into a spreadsheet as follows: # of Items Discount Rate 0 0% 5 4% 10 8% 1 The Price is Right is a TV game show in which contestants try to guess the price of various items. The winner of each round is the person who guesses the highest price without going over the actual retail price.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 02/04/2011 for the course CS 100 taught by Professor Bb during the Spring '11 term at University of Warsaw.

### Page1 / 22

Section3.0 - Module 3 Introduction Having learned the...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online