Chapter 5 Retrieving Data for Computation Analysis and Reference Sales Creating Product Order Forms for Equipment Purchases Conceptual Review FALSE 1 The lookup value of a VLOOKUP function can be a contiguous cell range 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 3 The result vector of a LOOKUP function must be sorted in ascending TRUE order FALSE arguments FALSE functions is FALSE FALSE table TRUE values in cell D13 FALSE cell D13 TRUE 4 Reference and Lookup functions may not contain nested functions as 5 The default range lookup type for the VLOOKUP and HLOOKUP 6 Excel matches the lookup value tom with the entry TOM in a lookup 7 The row and column arguments in the INDEX function can be numeric 8 The formula INDEX B2 D7 B12 D17 B22 D17 2 3 2 returns the value in 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 10 The formula MATCH 40 10 40 50 90 0 returns the value 2 FALSE 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 12 What is the difference between the LOOKUP function and the VLOOKUP N A appears in the cell or HLOOKUP function The LOOKUP function finds the greatest value that does not exceed a specified value anywhere in a range table The LOOKUP function does not have True False and always gives an estimate not an exact match You can only use a LOOKUP function when you want to retrieve a value that is stored to the left of a key data column in a vertical lookup table or above a key data row in a horizontal lookup table Use VLOOKUP or HLOOKUP when the data you are looking for is in the first column row 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 You should use the INDEX function to find a value in a two dimensional table 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 Thursda y Friday Saturday 15 What is the difference between the LOOKUP function and the MATCH function The difference is while 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 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 a For orders under 20 there is a 5 delivery fee b For orders at least 20 but less than 50 there is an 8 delivery fee c For orders over 50 delivery is free of charge i 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 I made a chart that has the total order row across with the values x 20 x 20 x 50 Under each respective one I have 5 8 Free 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 A 2 D 3 2 TRUE 19 As shown in the Grades worksheet final grades are determined using the following grading scheme a Students earning over 900 points receive an A b Students earning less than 900 points but at least 800 points c Students earning less than 800 points but at least 700 points d Students earning less than 700 points but at least 600 points receive a B receive a C receive a D e Students earning less than 600 points receive an F i 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 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 whereas the lookup table A5 E6 is sorted in ascending order which allows you to use the HLOOKUP function with a TRUE lookup type Chapter 6 Evaluating the Financial Impact of Loans and Investments Finance Forecasting Cash Flows for a Capital Project Analysis 1 Function to calculate the interest percentage per period of a financial transaction RATE PV FV transaction PMT transaction NPER Type 2 Function to calculate the value at the beginning of a financial transaction 3 Function to calculate the value at the end of a financial transaction 4 Function to calculate periodic payments into or out of a financial 5 Function to calculate the number of compounding periods in a financial 6 Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period 7 This type of interest is calculated based on original principal regardless of 8 This type of interest is calculated based on principal and previous interest the previous interest earned Compound interest earned Simple interest 9 Function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value 10 Function to calculate the amount of a specific periodic payment that is principal in a given period 11 Function to calculate the amount of a periodic payment that is interest in a SLN PPMT given period IPMT 12 Function to calculate the cumulative principal paid between two periods 13 Function to determine the value of a variable set of cash flows discounted 14 Function to determine the rate of return where the net present value of CUMPRINC to its present value the cash flows is 0 NPV IRR 15 Assume that you are investing 6 000 in a savings plan today and will make additional contributions of 200 per quarter The plan pays 5 interest per year compounded quarterly Write an Excel formula to determine how much your savings will be worth in five years FV 5 0 4 5 4 200 6000 0 16 Write an Excel formula to determine the yearly interest rate being charged by the bank on a 375 000 30 year …
View Full Document
Unlocking...