Page 1 Final Exam Study Guide Answers Spring 2014 CGS 2518 Chapters 5-9 Answers to all Conceptual Review Chapter 5 1. False The lookup_value of a Vlookup function can be contiguous cell range 2. True 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. False The result_vector of a lookup function must be sorted in ascending order 4. False Reference and lookup functions may not contain nested functions as arguments 5. False The default range_lookup type for the Vlookup and Hlookup functions is false 6. True Excel matches the lookup_value "tom" with the entry "TOM" in a lookup table 7. True The rows and column arguments in the INDEX functions can be numeric values, Boolean values, or text 8. True The formula =INDEX((B2:D7,B12:D17,B22:D17),2,3,2) returns the value in cell D13 9. False The formula =Average(Choose(1,B12:D17,B22:D17)) average the value 1 with the values in cells B12 to D17 and B22 D17 10. True 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? #N/A 12. What is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP function? The LOOKUP function looks up avlues anywhere in a table whereas the VLOOKUP and HLOOKUP can only retrieve data from tables with vertical and horizontal orientation, respectively. 13. Which excell function should you use when you want to look up a value from a two dimensional table, where both columns and rows can be varied? INDEX 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? A LOOKUP function looks up the greatest value that does not exceed a specified value anywhere in a table or range whereas a MATCH function returns the relative position of a matched value in a list.Page 2 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,$A2:$B7,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: Fill the chart with the rows equaling >=20, >=50 and 5.00, 8.00, and 0 for the respective prices. 18. Write a formula in cell C13 in the pricing worksheet to lookup 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)Page 3 Base your answers for Questions 19–20 on the Scores and Grades worksheets shown here. 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 C2 in the Scores worksheet that determines the final grade forthe first student based on the grading scheme. Use the appropriate Reference andLookup 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 in cells A5:E6 is not in ascending order and therefore cannot work correctly.Page 4 Chapter 6 1. Rate - Calculates the interest percentage per period of a financial transaction 2. PV - Calculates the value at the beginning of a financial transaction 3. FV - Calculates the value at the end of a financial transaction 4. PMT - Calculates periodic payments into or out of a financial transaction 5. NPER - Calculates the number of compounding periods in a financial transaction 6. Type - Use a 1 for this argument to indicate that interest will be paid at the beginning 7. Compound Interest – This type of interest is calculated based on principal and previous interest earned 8. Simple Interest - This type of interest is calculated based on original principal regardless of the previous interest earned 9. SLN - Calculates straight line depreciation based on the initial capital investment, number of years to be depreciated, and salvage value. 10. PPMT - Calculates the amount of a specific periodic payment that is principal in a given period 11. IPMT - Calculates the amount of a periodic payment that is interest in a given period 12. CUMPRINCE - Calculates the cumulative principal paid between two years 13. NPV - Determines the value of a variable set of cash flows discounted to its present value 14. IRR - Determines the rate of return, where the net present value of the cash flows is 0 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. Formula for savings in 5 years =FV(.05/4, 5*4, -200, -6000,0) 16. Excel formula to determine the yearly interest rate being charged by the bank on a $375,000, 30-year mortgage. You make a monthly mortgage payment of $3,000 and the value of the loan at the end of 30 years is 0. Interest is compounded monthly. =RATE(30*12,-3000,375000,0,0)
View Full Document
Unlocking...