Chapter 5 Conceptual Questions and Answers 1 The lookup value of a HLOOKUP function can be a contiguous cell range Answer 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 Answer True 3 The result vector of a LOOKUP function must be sorted in ascending order Answer False 4 Reference and Lookup functions may not contain nested functions as arguments Answer False 5 The default range lookup type for the VLOOKUP and HLOOKUP fu nctions is FALSE Answer False 6 Excel matches the lookup value tom with the entry tom in a l ookup table Answer True 7 The row and column arguments in the INDEX function can be num eric values Boolean values or text Answer False 8 The formula INDEX B2 D7 B12 D17 B22 D17 2 3 1 returns the value in cell D3 Answer 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 Answer False 10 The formula MATCH 40 10 20 40 50 0 returns the value 3 Answer True 11 What happens when Excel is solving a HLOOKUP formula with a FALSE range lookup type and does not find an exact match in the lookup table Answer N A 12 What is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP function Answer VLOOKUP searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table HLOOKUP is similar but it searches a horizontal lookup table The LOOKUP function looks for a matching value in a range of cells vertical or horizontal that you specify and returns a value LOOKUP is limited to a single row or column 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 Answer To find a value in a two dimensional table use the INDEX function 14 Write a formula to choose the name of the third day of the week from the list starting with Sunday Monday Tuesday Saturday Answer CHOOSE 3 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 15 What function returns the relative position of an item from a list Answer MATCH 16 Write a formula in cell C13 in the Pricing worksheet to determine the total cost of making copies for this order 275 copies Write the formula so that it works when copied into cells C14 C15 Answer NPER 17 The delivery charges used in the delivery worksheet are as follows a For orders under 10 there is a 6 fee b For orders at least 10 but less than 40 there is a 50 fee c For orders over 40 the delivery is free Answer C B A Delivery Charges 10 10 40 6 7 50 Free 1 2 3 18 Write a formula in cell D13 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 works when copied down the column Answer VLOOKUP A2 A3 B3 3 True Word wont let me rotate this picture 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 points but at least 800 points receive a B Students earning less than 800 points but at least 700 points receive a C Students earning less than 700 points but at least 600 points receive a D Students earning less than 600 points receive an F Write a formula in cell C3 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 Answer HLOOKUP B2 Grades A 5 E 7 2 TRUE 20 Explain the difference between the lookup table in cells A3 E4 of the Grades worksheet and the lookup table in cells A5 E7 in the same worksheet Answer The lookup table in A3 E4 of the Grades worksheet is sorted in descending order and the lookup table in cells A5 E7 is sorted in ascending order which allows you to use the HLOOKUP function with a TRUE lookup type Chapter 6 Conceptual Questions and Answers 1 Function to calculate the value at the end of a financial transaction Answer FV 2 Function to Calculate the interest percentage per period of a financial transactionAnswer RATE Function to calculate the value at the beginning of a financial transaction Answer PV Function to calculate the number of compounding periods in a financial transaction Answer NPER Function to calculate periodic payments into or out of a financial transaction Answer PMT 3 4 5 6 Use a 0 for this argument to indicate that interest will be paid at the beginning of each compounding period Answer Type 7 This type of interest is calculated based on original principal regardless of the pervious interest earned Answer Simple Interest 8 This type of interest is calculated based on principal and pervious interest earnedAnswer Compound Interest 9 Function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value Answer SLN10 Function to calculate the cumulative interest paid between two periods Answer CUMIPMT 11 Function to calculate the amount of a period payment that is interest in a given period Answer IPMT 12 Function to calculate the amount of a specific periodic payment that is principal in a given period Answer PPMT 13 Function to Determine the value of a variable set of cash flows discounted to its present value Answer NPV 14 Function to determine the rate of return where the net present value of the cash flows is 0 Answer IRR 15 Assume that you have been left an inheritance and want to save part of it toward the purchase of a car upon graduation which is three years from now Write an Excel formula to determine the amount of money you need to invest now to have 17 000 at the end of the three year period Assume that you will place this money in a CD that pays 2 interest compounded quarterly and that you will be making no additional deposits into this account Answer PV 02 4 3 4 0 17000 16 Write an Excel formula to determine the yearly interest rate being charged by the bank on a 400 000 20 year mortgage You make a monthly mortgage payment of 3 400 and the value of the loan at the end of 20 years is 0 Interest is compounded monthly Answer RATE 20 12 3400 400000 0 17 Assume you are buying a car for 21 500 with a 4 000 down payment and you are borrowing the rest from a bank at 5 5 annual interest …
View Full Document
Unlocking...