CHAPTER 5 REVIEW Answer Questions 1 10 as True or False 1 The lookup value of a VLOOKUP function can be a contiguous cell range FALSE 2 In a VLOOKUP formula with a TRUE lookup type the first column of the lookup table referenced must be in ascending order to retrieve the correct value TRUE 3 The result vector of a LOOKUP function must be sorted in ascending order FALSE FALSE FALSE FALSE TRUE 4 Reference and Lookup functions may not contain nested functions as arguments 5 The default range lookup type for the VLOOKUP and HLOOKUP functions is 6 Excel matches the lookup value tom with the entry TOM in a lookup table 7 The row and column arguments in the INDEX function can be numeric values Boolean values or text FALSE 8 The formula INDEX B2 D7 B12 D17 B22 D17 2 3 2 returns the value in cell D13 TRUE 9 The formula AVERAGE CHOOSE 1 B12 D17 B22 D17 averages the value 1 with the values in cells B12 to D17 and B22 to D17 FALSE 10 The formula MATCH 40 10 40 50 90 0 returns the value 2 TRUE 11 What happens when Excel is solving a VLOOKUP formula with a FALSE range lookup type and does not find an exact match in the lookup table The text N A is displayed in the cell 12 What is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP function VLOOKUP function searches a specified part of a worksheet for data starting in the first column V in Vlookup stands for vertical HLOOKUP function is similar to VLOOKUP except that it searches a horizontal lookup table in which data is stored in rows instead of columns Unlike VLOOKUP the LOOKUP function looks up the greatest value that does not exceed a specified value anywhere in a table or range LOOKUP also uses a TRUE lookup type so the column or row containing the lookup values must be in ascending order 13 Which Excel function should you use when you want to look up a value from a two dimensional table where both the columns and rows can be varied To find a value in a two dimensional table use the INDEX function 14 Write a formula to choose the name of the fifth day of the week from the list starting with Sunday Monday Tuesday Saturday CHOOSE 5 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 15 What is the difference between the LOOKUP function and the MATCH function Although the LOOKUP function returns a corresponding value in a list the MATCH function returns the relative position number of the lookup value in a list MATCH function returns the relative position such as 1 2 or 3 of the item in a list Base your answers for Questions 16 18 on the Pricing and Delivery worksheets shown here Cells Pricing A1 B7 list the costs per copy based on the total number of copies being made The price for less than 10 copies corresponds to 0 07 per copy the price for at least 10 copies but less than 100 copies corresponds to 0 06 per copy etc 16 Write a formula in cell B13 in the Pricing worksheet to determine the total cost of making copies for this order 3 copies Write the formula so that it works when copied into cells B14 B16 VLOOKUP A13 A 2 B 7 2 TRUE A13 17 The delivery charges used in the Delivery worksheet are as follows For orders under 20 there is a 5 delivery fee For orders at least 20 but less than 50 there is an 8 delivery fee For orders over 50 delivery is free of charge Create a lookup table for the Delivery worksheet so that you can use a lookup function to calculate the delivery cost for each order Organize the table in a horizontal format as shown here A B C D Delivery Charges 1 2 3 4 1 20 50 5 8 0 18 Write a formula in cell C13 in the Pricing worksheet to look up the correct delivery cost using the lookup table you created in Question 17 Write the formula so that it can be copied down the column HLOOKUP B13 B 1 D 2 2 TRUE Base your answers for Questions 19 20 on the Scores and Grades worksheets shown here A 900 A 0 F B 800 B 600 D C 700 C 700 C D 600 D 800 B E 0 F 900 A C Final Grade A Name 1 2 Mary 3 Davide 4 Tang 5 Bindu 6 Thomas 7 Rebecca 8 Mallory B Score 930 450 880 750 320 850 970 1 2 3 4 5 6 7 8 9 19 As shown in the Grades worksheet final grades are determined using the following grading scheme Students earning over 900 points receive an A Students earning less than 900 pts but at least 800 pts receive a B Students earning less than 800 pts but at least 700 pts receive a C Students earning less than 700 pts but at least 600 pts receive a D Students earning less than 600 pts receive an F Write a formula in cell C2 in the Scores worksheet that determines the final grade for the first student based on the grading scheme Use the appropriate Reference and Lookup function and write the formula so that it can be copied down the column HLOOKUP B2 Grades A 5 E 6 2 TRUE 20 Explain the difference between the lookup table in cells A1 E2 of the Grades worksheet and the lookup table in cells A5 E6 in the same worksheet The LOOKUP table A1 E2 is sorted in descending order The LOOKUP table in A5 E6 is sorted in ascending order which allows you to use the HLOOKUP function with a TRUE lookup type Chapter 6 Conceptual Review Match the following lettered items with Questions 1 14 A Compound Interest B CUMPRINC C FV D IPMT E IRR F NPER G NPV H Payback Period I PMT J PPMT K PV L RATE M ROI N Simple Interest O SLN P Type 1 L RATE Function to calculate the interest percentage per period of a financial transaction transaction I PMT transaction 2 K PV Function to calculate the value at the beginning of a financial 3 C FV Function to calculate the value at the end of a financial transaction 4 Function to calculate the periodic payments into or out of financial 5 F NPER Function to calculate the number of compounding periods in a financial transaction 6 P TYPE Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period 7 N Simple Interest This type of interest is calculated based on original principal regardless of the previous interest earned 8 A Compound Interest This type of interest is calculated based on principal and previous interest earned 9 O SLN Function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value 10 J PPMT …
View Full Document
Unlocking...