CGS2518 Final Chapter 5 1 2 the lookup value of a VLOOKUP function can be a contiguous cell range FALSE 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 4 Reference and Lookup functions may not contain nested functions as FALSE arguments FALSE FALSE FALSE lookup table TRUE cell D13 TRUE 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 7 The row and column arguments in the INDEX formula 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 9 The formula AVERAGE CHOOSE 1 B12 D17 B22 D17 averages the value 1 with the values in the 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 a The VLOOKUP function returns a N A 12 What is the difference between the LOKOUP function and the VLOOKUP or HLOOKUP function a The LOOKUP function looks up the greatest value that doesn t exceed a specified value anywhere in a table or range It doesn t have True False It always gives an estimate not an exact match 13 Which Excel function shold you use when you want to look up a value from a two dimensional table where both the columns and rows can be varied a 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 staring with Sunday Monday Tuesday Saturday a CHOOSE 5 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 15 what is the difference between the LOOKUP and MATCH function a The LOOKUP function returns a corresponding value in a list while the MATCH function returns the relative position number of the lookup value in a list 16 Write a forumula in ell 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 in to cells b14 b16 a VLOOKUP A13 A 2 B 7 2 TRUE A13 17 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 Horizontally a Order Value BLAH BLAH 18 Write a formula in the Pricing Worksheet to look up the correct delivery cost using the lookup table you created pizza delivery charges Write the formula so that it can be copied down the column a HLOOKUP b13 b 1 S 2 2 True 19 Write a formula in cell C2 in the scores worksheet page 355 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 a HLOOKUP B2 Grades A 5 E 6 2 TRUE 20 Explain the difference between the lookup table in cell A1 E2 of the Grades worksheet and the lookup table in cell A5 E6 in the same worksheet a 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 1 2 3 4 5 function to calculate the interest percentage per period of financial transaction RATE function to calculate the value at the beginning of a financial transaction PV function to calculate the value at the end of a financial transaction FV function to calculate periodic payments into or out of a financial transaction PMT function to calculate the number of compounding periods in a financial transascton NPER 6 use a 1 for this argument to indicate that interest will be paid at the 7 8 beginning of each compounding period TYPE this type of interest is calculated based on original principal regardless of the previous interest earned simple interest this type of interest is calculated based on principal and previous interested earned compound interest function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value SLN 10 function to calculate the amount of a specific periodic payment that is 9 princiapal in a given period PPMT 11 function to calculate the amount of a period payment that is interest in a 12 function to calculate ethe cumulative principal apid between two periods 13 Funtion to determine the value of a variable set of cash flows discounted to 14 Function to determine the rate of return where the net present value of the given period IPMT CUMPRINC its present value NPV cash flows is 0 IRR 15 Assume that you are investing 6 000 in a savings plan today and will make addition contributions of 200 perquarter The plan pays 5 interest per year compounded quarterly Formula for savings in 5 years a FV 5 0 4 5 4 200 6000 0 16 the yearly interest rare being charged by the bank on a 375 000 30 year mortgage You make a monthly mortgage payment of 3000 and the value of the loan at the end of 30 years is 0 Interest is compounded monthly a RATE 30 12 3000 375000 0 17 you are buying a car for 23 500 with a 3 000 down payment and you are borrowing the rest from a bank at 6 0 annual interest compounded monthly Your monthly payments are 370 Write an excel formula to determine the number of years it will take you to pay off this loan a NPER 6 0Z 12 379 20500 0 18 consider a 100 000 mortgage at5 annual interest compounded monthly to be paid back over the next 15 years The loan will have a 5000 balloon payment due at the end of the loan Write an excel formula to determine the payment that must be made each month on this loan a PMT 5 0 12 15 12 100000 5000 19 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 15000 at the end of the three year period Assume that you will place this money in a CD that pays 3 interest compounded quarterly and that you will be making no additional deposits into this account a PV 3 0 4 3 4 0 15000 20 write and excel formula to determine the amount of money …
View Full Document
Unlocking...