Ch3ClassNotes_Part4_sp11

# Ch3ClassNotes_Part4_sp11 - Chapter 3: Excel Functions, Part...

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

Chapter 3: Excel Functions, Part 4 (of 4): Lookup Functions Chapter Skills for Chapter 3, Part 4 Use the VLOOKUP & HLOOKUP functions to return a match based on a numeric range . Use the VLOOKUP & HLOOKUP functions to return a match based on an alphabetic text range . Use the VLOOKUP & HLOOKUP functions to return an exact match . Build formulas that respond to error values that are produced by some portion of the formula. Chapter Vocabulary for Chapter 3, Part 3 VLOOKUP (& HLOOKUP) function (know arguments, including the last argument which is optional). o lookup value o lookup table (range) o column index (or row index in the case of HLOOKUP) o range match (optional) range match versus exact match (be sure to understand the difference) #VALUE! (know what causes this error value) #N/A (as it relates to lookup functions) IFERROR function Chapter Demonstration 14: Use nested IF functions to assign interests rate based on credit score ranges 1. Re-open the file you saved from the previous class demonstration, the loan calculator exercise. 2. Revise the loan inputs so that the interest rate is determined by the customer's credit score (as is done routinely in practice). a. In cell A7, add a label for the customer's credit score: Credit Score b. In cell B7, enter an appropriate value for the customer's credit score (for now enter a value between 500 and 850). 3. In the interest rate cell (B6), enter a formula that assigns an interest rate based on the customer’s credit score. The following business rules apply (credit scores are whole numbers): For credit scores of 800 or greater, interest rate is 3%. For credit scores of 700 to 799, interest rate is 6%. For credit scores of 600 to 699, interest rate is 9%. For credit scores of 500 to 599, interest rate is 12%. For credit scores less than 500, the customer is disqualified for a loan. a. Consider the logic required to implement this formula. a.i. In order to test for multiple ranges, you must start at either the highest range or the lowest range and apply each subsequent range test in the appropriate order (descending or ascending).

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

View Full Document
a.ii. Since the credit score can fall in only one range, we are implementing OR logic. Only one interest rate can apply. a.iii. As you test each range (starting at highest or lowest), you are eliminating that range from subsequent tests. For example, if you test for credit scores >=800 first, when you test for scores >=700, the >=800 range has already been tested. If that range test of >=800 is true, then the second range will never be tested (based on
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 11/21/2011 for the course ACIS 2504 taught by Professor Mtgriffin during the Spring '08 term at Virginia Tech.

### Page1 / 6

Ch3ClassNotes_Part4_sp11 - Chapter 3: Excel Functions, Part...

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

View Full Document
Ask a homework question - tutors are online