Class Notes (786,073)
Canada (481,982)
CS 100 (114)
Dan Brown (12)

Module 3 notes.docx

5 Pages
Unlock Document

University of Waterloo
Computer Science
CS 100
Dan Brown

Module 3 notes 3.1 The VLOOKUP Function VLOOKUP (vertical lookup) provides this functionality for a table in which each row represents one entry. In general, VLOOKUP takes four parameters as follows: 1. the value to match in the lookup table (the search key) usually the search key is stored in some cell, and the address of the cell containing the search key is given as the first parameter to VLOOKUP 2. the table to search for a match (the key is matched against the leftmost column) The table specified in the second parameter is usually a named range that you must define prior to entering the VLOOKUP function in a formula. 3. the column in which to find the value to be returned (the leftmost column is column 1) The first column of the table contains possible keys to match, and the other columns contain possible return values. The third parameter must be a column number between 1 and the number of columns in the table. It is the contents of the cell in that column of the matching row that is returned as the value of VLOOKUP. 4. whether the closest match is to be found (TRUE) or an exact match is required (FALSE)The fourth parameter simply states, “Should I find the closest match, or is an exact match required?” FALSE, as used in this example, means an exact match to the key must be found in the first column of the table. 5. Excel CALC[1] “?” “.” matches any single character “*” “.*” matches any sequence of zero or more characters “~?” “\?” matches a question mark “.” “\.” matches a period “~*” “\*” matches an asterisk For example, “sm?th*” in Excel (or “*” in CALC) matches “Smith” or “Smythe” or “smatherson” and so forth; “sm*th*” (or “sm.*th.*” in CALC) matches any of those as well as “smoothly” and so forth. The pattern “*~? *” in Excel (or “.*\? .*” in CALC) matches any label that has a question mark followed by a blank (space character) somewhere within it. (Pattern matching cannot be used to match cell contents stored as numeric values.) 3.1.1 Exact match searching The fourth parameter for VLOOKUP indicates what to do when a match for the search key specified is not found in the table. If the fourth parameter is FALSE and the search key is not found, then VLOOKUP returns #N/A; this result can be handled by using the ISNA function When exact matching is specified, it is possible that your search key will match more than one value in the first column of the table, especially if you are searching with a label pattern; in this case, the first entry to match (i.e., the match nearest the top of the table) is returned by VLOOKUP. 3.1.2 Closest match searching When the fourth parameter is TRUE (or it is omitted altogether), “Price is Right”[2] 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. * To use lookup with inexact matching, the table must be sorted in increasing order of the value in its leftmost column. 3.2 Indirect addressing To make the application treat a label as a cell address or range of cell addresses, or as the name of a cell or a cell range, you must use the function INDIRECT. In its simplest form (and the only one we will use in CS 100), INDIRECT takes a label as its only parameter and treats that label as if it were a cell address or cell range. The result can then be used in another function if you wish. For example, using the function INDIRECT("A5") causes the label "A5" to be treated as if it were the address of a cell, namely A5. The formula =INDIRECT(C2) treats the contents of C2 as if it were the address of a cell (i.e., the value you want is not in cell C2, instead the contents of C2 tells you where to fetch the value you want); the formula returns the contents of the cell that has the address specified by the label stored in cell C2 (e.g., if C2 contains the label "G14", then the formula returns the contents of cell G14; if C2 contains the label "SalesTax", then the formula returns the contents of the cell named SalesTax). INDIRECT Takes one parameter (a label) and interprets that label as a cell address (e.g., A4), a cell range (e.g., A4:B17), or a name assigned to a cell or to a cell range. SUMPRODUCT Takes two parameters (two cell ranges of equal shape) and computes the sum of the products of the corresponding cell values in each range. 3.3 Spreadsheet Design A spreadsheet is a convenient way to perform complex calculations, especially when those calculations are highly repetitive. However, to be useful a spreadsheet must beverifiable and maintainable. This means that a spreadsheet is not only a communications medium between its creator and the computer, but it is also a communication medium among its creator, maintainer(s), and all its users. A spreadsheet will often be useful over several years, and so e
More Less

Related notes for CS 100

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.