CGS2518 FINAL EXAM NOTES STUDY FOR TEST CHAPTER 5 1 The lookup value of a VLOOKUP function CAN NOT 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 DOES NOT HAVE TO BE sorted in ascending order 4 Reference and Lookup functions CAN contain nested functions as arguments 5 The default range lookup type for the VLOOKUP and HLOOKUP functions is NOT FALSE 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 NOT Boolean values or text 8 The formula INDEX B2 D7 B12 D17 B22 D17 2 3 2 returns the value in cell D13 9 The formula AVERAGE CHOOSE 1 B12 D17 B22 D17 DOES NOT average 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 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 N A 12 What is the difference between the LOOKUP 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 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 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 starting 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 1 of 9 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 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 D 2 2 TRUE 19 Write a formula in cell C2 in the Scores worksheet that determines the final grade for the student based on the grading scheme a 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 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 Function to calculate interest per period of a financial transaction 2 Function to calculate the value at the beginning of a financial transaction 3 Function to calculate value of the end of a financial transaction 4 Function to calculate periodic payments in or out of a financial transaction 5 Function to calculate the number of compounding periods in a financial transaction 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 the previous interest earned Simple Interest Compound Interest depreciated and salvage value 8 This type of interest is calculated based on principal and previous interest earned 9 Function to calculate straight line depreciation based on the initial capital investment number of years to be 2 of 9 RATE PV FV PMT NPER Type SLN PPMT IPMT NPV IRR 10 Function to calculate the amount of a specific period payment that is principal in a given period 11 Function to calculate the amount of a period payment that is interest in a given period 12 Function to calculate the cumulative principal paid between two periods CUMPRINC 13 Function to determine the value of a variable set of cash flows discounted to its present value 14 Function to determine the rate of return where the net present value of cash flows is equal to zero 15 Assume that you are investing 6 000 in a savings plan today and will make additional contributions of 200 quarter The plan pays 5 interest yr compounded quarterly How much will savings be in 5 years FV 05 4 4 5 200 6000 16 Write an excel formula to determine the yearly interest rate being charged by the bank on a 375 000 30yr mortgage You make a monthly mortgage payment of 3000 and the value of the loan at the end of 30 years is 0 RATE 3012 3000 375000 12 17 Assume you re buying a car for 23 500 with a 3 000 down pmt and borrowing the rest from a bank at 6 0 annual interest compounded monthly Your monthly payment is 370 Write formula to determine yrs to pay off loan NPER 06 12 370 20500 12 18 Consider a 100k mortgage at 5 annual interest compounded monthly to be paid back over the next 15 years The loan will have 5 000 balloon pmt due at the end of the loan Write formula to determine the pmt that must be made each month on this loan PMT 05 12 15 12 100000 5000 19 Write a formula to determine the amount of money you need to invest now to have 15 000 at the end of the three year period 3 interest compounded quarterly no additional deposits PV 0 03 4 3 4 0 15000 20 Write formula to determine the amt of money that can be depreciated each year using straight line for equipment that cost 250 000 w useful life of 10 years and Salvage Value of 10 000 SLN 250000 10000 10 CHAPTER 7 3 of 9 1 List and describe the steps you would take to create a structured list of data from a text file that contains values stored on separate lines c d a Determine format needed most likely Comma separated values CSV b Copy data from file and paste into workbook In the column next to the data use CONCATENATE A1 A2 A3 A4 …
View Full Document
Unlocking...