Exploring: Microsoft Excel 2013, Comprehensive Mary Anne Poatsy solutions manual and test bank
Exploring Microsoft Excel 2013, Comprehensive (Poatsy)
Chapter 2 Formulas and Functions: Hands-On-Exercise Videos
1) When copying a cell, the term absolute cell reference indicates ________.
A) the value of the cell will not change
B) the cell label will not change
C) cell reference will not change
D) the cell can only be referenced one time
Answer: C
Diff: 2
Objective: HOE 1: Formula Basics
2) =D$3 is an example of a(n) ________ cell reference.
A) relative
B) absolute
C) mixed
D) combined
Answer: C
Diff: 2
Objective: HOE 1: Formula Basics
3) When cell C3 containing the formula =B6*C8 is copied to cell D2, the formula in cell D2 is ________.
A) =B6*C8
B) =B6*B7
C) =C5*D8
D) =C5*D7
Answer: B
Diff: 2
Objective: HOE 1: Formula Basics
4) A formula that refers to its own cell creates a(n) ________ reference.
A) circular
B) cross
C) absolute
D) erroneous
Answer: A
Diff: 2
Objective: HOE 1: Formula Basics
5) The MEDIAN function is used to find the ________ number in a range of cells.
A) average
B) middle
C) lowest
D) highest
Answer: B
Diff: 2
Objective: HOE 2: Function Basics
6) The value of =C3+C4+C5+C6+C7+C8+C9 can be calculated by using ________.
A) =TOTAL(C3:C9)
B) =SUM(C3:C9)
C) =TOTAL(C3...C9)
D) =SUM(C3...C9)
Answer: B
Diff: 2
Objective: HOE 2: Function Basics
7) If you wanted to determine how many cells in a range are not empty, you would use the ________ function.
A) COUNTA
B) COUNT
C) MEDIAN
D) MAXIMUM
Answer: A
Diff: 2
Objective: HOE 2: Function Basics
8) Which Excel function displays the current date?
A) CURRENT
B) DAY
C) NOW
D) TODAY
Answer: D
Diff: 2
Objective: HOE 2: Function Basics
9)
The expression that implements the flow chart shown in the above figure is ________.
A) IF(E2>B$2,0,B$3)
B) IF(E2>B$2,B$3,0)
C) IF(B$3,0,E2>B$2)
D) IF(B$3,E2>B$2,0)
Answer: B
Diff: 2
Objective: HOE 3: Logical, Lookup, and Financial Functions
10)
Using the table shown above, you would use the ________ function to find interest rates corresponding to the number of years.
A) HLOOKUP
B) VLOOKUP
C) IF
D) INDEX
Answer: B
Diff: 2
Objective: HOE 3: Logical, Lookup, and Financial Functions
11) The name for a range can begin with a letter or ________.
A) underscore (_)
B) hyphen (-)
C) number
D) period (.)
Answer: A
Diff: 3
Objective: HOE 4: Range Names
12) A range name can contain up to ________ characters.
A) 25
B) 100
C) 255
D) 300
Answer: C
Diff: 3
Objective: HOE 4: Range Names
13) =D5 is an example of an absolute cell reference.
Answer: FALSE
Diff: 1
Objective: HOE 1: Formula Basics
14) =$R$5 is an example of a mixed cell reference.
Answer: FALSE
Diff: 1
Objective: HOE 1: Formula Basics
15) To tally a column, you would use the COUNT function.
Answer: TRUE
Diff: 2
Objective: HOE 2: Function Basics
16) The TODAY and NOW functions return the same result.
Answer: FALSE
Diff: 2
Objective: HOE 2: Function Basics
17) AVERAGE and MEDIAN are two functions used to determine central tendencies.
Answer: TRUE
Diff: 3
Objective: HOE 2: Function Basics
18) The IF function is an example of a Logical function.
Answer: TRUE
Diff: 1
Objective: HOE 3: Logical, Lookup, and Financial Functions
19) The PMT function calculates the amount of a mortgage.
Answer: FALSE
Diff: 2
Objective: HOE 3: Logical, Lookup, and Financial Functions
20) Range names are edited and deleted using the Name Manager.
Answer: TRUE
Diff: 1
Objective: HOE 4: Range Names
Instructor’s Manual Materials to Accompany
EXCEL CHAPTER 2: Formulas and Functions: Performing Quantitative Analysis
Available Instructor Resources
| Resource | File Name | Found |
| Student Data Files | various | Online Instructor Resource Center |
| Solution Files | various | Online Instructor Resource Center |
| Answer Keys | Online Instructor Resource Center | |
| Matching | e02_answerkey_match | |
| Multiple Choice | e02_answerkey_mc | |
| Concepts Checks | e02_answerkey_concepts | |
| Scorecards | e02b1Tips_scorecard | Online Instructor Resource Center |
| Scoring Rubrics | e02b1Tips_rubric | Online Instructor Resource Center |
| Annotated Solution File | e02b1Tips_annsolution | Online Instructor Resource Center |
| Scripted Lecture (Script) | e02_script | Online Instructor Resource Center |
| Scripted Lecture Solution | e02_script_solution | |
| Scripted Lecture Data | e02_script_data | |
| PowerPoint Presentation | e02_powerpoints | Online Instructor Resource Center |
| Testbank | e02_testbank | Online Instructor Resource Center |
| Instructor's Manual (lesson plans incl.) | e02_instructormanual | Online Instructor Resource Center |
| Assignment Sheet | e02_assignsheet | Online Instructor Resource Center |
| Prepared Exam (Chapter & App) | Online Instructor Resource Center | |
| Prepared Exam-Chap instruction | e02_exam_chap_instruction | |
| Prepared Exam-Chap solution | e02_exam_chap_solution | |
| Prepared Exam-Chap Data | e02_exam_chap_data | |
| e02_exam_chap_annsolution | ||
| Prepared Exam-Chap Scorecard | e02_exam_chap_scorecard | |
| Prepared Exam-App instruction | e02_cumexam_instruction | |
| Prepared Exam-App solution | e02_cumexam_solution | |
| Prepared Exam-App Data | e02_cumexam_data | |
| Prepared Exam-App Annotated Sol. | e02_cumexam_annsolution | |
| Prepared Exam-App scorecard | e02_cumexam_scorecard | |
| File Guide | e02_file_guide | Online Instructor Resource Center |
| Instructor Resource Card | e02_ircard | Online Instructor Resource Center |
| Objective Map | e02_objectivesmap | Online Instructor Resource Center |
| Online Chapter Review | e02_chapt_checklist | Companion Website for Students |
| Grader Project | ||
| Grader-instruction | e02_grader_instruction | Online Instructor Resource Center |
| Grader-solution | e02_grader_solution | |
| Grader-data | e02_grader_data | |
| Grader-annoted. Solution | e02_grader_annsolution | |
| Grader-scorecard | e02_grader_scorecard | |
| Additional Projects (Practice & Mid Level) | Online Instructor Resource Center | |
| Additional Proj-Practice instruction | e02_p_addproject_instruction | |
| Additional Proj- Practice solutions | e02_p_addproject_solution | |
| Additional Proj-Practice Data | e02_p_addproject_data | |
| Additional Proj-Practice Ann Sol. | e02_p_addproject_annsolution | |
| Additional Proj-Practice Scorecard | e02_p_addproject_scorecard | |
| Additional Proj-Mid Level instruction | e02_ml_addproject_instruction | |
| Additional Proj-Mid Level solutions | e02_ml_addproject_solution | |
| Additional Proj-Mid Level Data | e02_ml_addproject_data | |
| Additional Proj-Mid Level Ann Sol. | e02_ml_addproject_annsolution | |
| Additional Proj-Mid Level Scorecard | e02_ml_addproject_scorecard |
CHAPTER OBJECTIVES
When students have finished reading this chapter, they will be able to:
▪ Use relative, absolute, and mixed cell references in formulas
▪ Correct circular references
▪ Insert a function
▪ Insert basic math and statistics functions
▪ Use date functions
▪ Determine results with the IF function
▪ Use lookup functions
▪ Calculate payments with the PMT function
▪ Create and maintain range names
▪ Use range names in formulas
CHAPTER OVERVIEW
The students will increase their understanding of formulas and learn how to build robust workbooks that perform a variety of calculations for quantitative analysis. The ability to build sophisticated workbooks and to interpret the results increases the value of an employee to any organization.
The major sections in this chapter are
1. Formula Basics. In this section, the student will learn how to create formulas where cell addresses change or remain fixed when copied. Also, students will learn how to identify and prevent circular references in formulas.
2. Function Basics. The student will learn to work with Excel functions as a predefined computation that simplifies creating a formula that performs a complex calculation.
3. Logical, Lookup, and Financial Functions. Lookup and reference functions are useful when you need to look up a value in a list to identify the applicable value. Financial functions are useful to anyone who plans to take out a loan or invest money. In this section, students will learn how to use the logical, lookup, and financial functions.
4. Range Names. To simplify entering ranges in formulas, range names can be used.
CLASS RUN-DOWN
1. Have students turn in Homework assignments.
2. Talk about chapter using discussion questions listed below.
3. Use PowerPoint Presentation to help students understand chapter content.
4. Demonstrate Excel 2013 formulas and functions.
5. Run through Scripted Lectures for chapter. Give special attention to areas where students might be challenged.
6. Have students complete Capstone Exercise for Excel Chapter 2.
7. Use myitlab for in-class work or to go over homework.
8. Give students Homework Handout for next class period.
LEARNING OBJECTIVES
At the end of this lesson students should be able to:
▪ Use a relative cell address.
▪ Use an absolute cell address.
▪ Use a mixed cell reference.
▪ Correct a circular reference.
▪ Understand function syntax.
▪ Insert basic math and statistics functions.
▪ Use date functions when needed.
▪ Design an IF function including logical text and value_if_true and value_if_false arguments.
▪ Use lookup functions such as VLOOKUP and HLOOKUP.
▪ Calculate payments with the PMT function.
▪ Create and maintain range names.
▪ Use range names in formulas.
KEY TERMS
ABS function – Displays the absolute (i.e., positive) value of a number.
Absolute cell reference–Indicates a cell’s specific location and provides a permanent reference to a specific cell; the cell reference does not change when you copy the formula (Example: $C$8).
Argument–An input, such as a cell reference, value, or arithmetic expression needed to complete a function.
AVERAGE function–Calculates the arithmetic mean, or average, of values in a range.
Breakpoint–Lowest value for a specific category or series in a lookup table.
Circular reference–Occurs when a formula directly or indirectly refers to the cell in which the formula is located.
COUNT function–Tallies the number of cells in a range that contain values.
COUNTA function–Tallies the number of cells in a range that are not empty.
COUNTBLANK function–Tallies the number of blank cells in a range.
Formula AutoComplete–Displays a list of functions and defined names that match letters as you type a formula.
FREQUENCY function–Counts how often values appear in a given range.
Function ScreenTip–A small pop-up description that displays the arguments for a function as you enter it.
Function–Predefined computation that simplifies creating a complex calculation. Excel provides more than 400 functions, which are organized into 14 categories.
HLOOKUP function–Looks up a value in a horizontal lookup table where the first row contains the values to compare with the lookup value.
IF function–Evaluates a condition and returns one value if the condition is true and a different value if the condition is false.
Insert Function dialog box–To browse a list of functions; useful if not sure of the function and need to see descriptions.
INT function–Rounds a value number down to the nearest whole number.
Lookup table–A range containing a table of values or text that can be retrieved. The table should contain at least two rows and two columns, not including headings.
MAX function–Identifies the highest value in a range.
MEDIAN function–Identifies the midpoint value in a set of values. Useful because extreme values often influence arithmetic mean calculated by the AVERAGE function.
MIN function–Displays the lowest value in a range.
Mixed cell reference–Combines both an absolute cell reference and a relative cell reference in a formula; the absolute part does not change but the relative part does when you copy the formula. When you copy a formula containing a mixed cell reference, either the column letter or the row number that has the absolute reference remains fixed while the other part of the cell reference that is relative changes in the copied formula (Examples: $C8 and C$8).
MODE.SNGL function – Displays the most frequently occurring value in a list.
Nested function–Occurs when one function is embedded as an argument within another function.
NOW function–Displays the current date and military time that the workbook was last opened.
PMT function–Calculates the periodic payment for a loan with a fixed interest rate and fixed term.
Quick Analysis–Set of analytical tools used to apply formatting, create charts or tables, and insert basic functions.
Range name–A word or string of characters that represents one or more cells and can be used in formulas instead of cell references.
RANK.AVG function–Identifies a value’s rank within a list of values; returns an average rank for identical values.
RANK.EQ function–Identifies a value’s rank within a list of values; the top rank is identified for all identical values.
Relative cell reference–Indicates a cell’s relative location from the cell containing the formula; the cell reference changes when the formula is copied (Example: C8).
ROUND function -- Rounds a value to a specific number of digits.
SUM function–Calculates total of values contained in two or more cells and displays the result in the cell containing the function.
Syntax–Set of rules that govern structure and components for properly entering a function.
TODAY function–Displays the current date.
VLOOKUP function–Looks up a value and returns a related result from the vertical lookup table.
DISCUSSION QUESTIONS
· What is the difference between using a relative cell reference, an absolute cell reference, and a mixed cell reference?
· What is a circular reference and why would you need to avoid it?
· What is the difference between an AVERAGE and a MEDIAN function?
· How can the Function Arguments dialog box assist you?
· What is a Lookup Table and what is it used for?
· How can range names be used?
· What information is required to use a PMT function?
WHEN USING SCRIPTED LECTURE IN CLASS, DEMONSTRATE HOW TO:
▪ Use a relative cell address compared to an absolute cell reference
▪ Use a mixed cell reference for an absolute reference
▪ Correct a circular reference
▪ Insert basic math and statistics functions
▪ Use a date function
▪ Enter an IF function for a situation that evaluates to true or false
▪ Use lookup functions such as VLOOKUP and HLOOKUP when a Lookup Table is used
▪ Calculate monthly payments with the PMT function
▪ Create and maintain range names
▪ Use range names in formulas
CONNECTIONS PRACTICAL PROJECTS AND APPLICATIONS
· Find the payment for different loan amounts, APRs, and number of years.
· Keep a food diary with associated calories for the day and use the SUM function to track daily calories.
· Using a spreadsheet that you have developed, identify the number of cells that contain certain data by using the COUNT, COUNTBLANK, and COUNTA functions.
· Use VLOOKUP to calculate federal withholding tax for payroll.
TEACHING NOTES
Formula Basics
The ability to build sophisticated workbooks and to interpret the results increases an individual's value to any organization.
A. Using Relative, Absolute, and Mixed Cell
· Demonstrate copying a formula and show that the relative cell reference changes.
· An absolute reference is a permanent pointer to a particular cell, indicated with $ before the column letter and row number, such as $B$5. When you copy the formula, the absolute cell reference does not change. Show the comparison between absolute reference and relative reference usage.
v Teaching Tips: The F4 key toggles (4-way) through relative, absolute, and mixed references. There are two different mixed references
v Teaching Tips: Demonstrate not using the Absolute Reference when it is needed and then how to fix the problem
· A mixed reference contains part absolute and part relative reference, such as $B5 or B$5. Either the column or row reference changes, while the other remains constant when you copy the formula.
B. Correcting Circular References
· Demonstrate that If a formula contains a direct or an indirect reference to the cell containing the formula, a circular reference exists.
v Teaching Tips: Excel displays a green triangle in the top-left corner of a cell if it detects a potential error in a formula. Click the cell to see the Trace Error button (yellow diamond with exclamation mark). When you click Trace Error, Excel displays information about the potential error and how to correct it. In some cases, Excel may anticipate an inconsistent formula or the omission of adjacent cells in a formula. For example, if a column contains values for the year 2016, the error message indicates that you did not include the year itself. However, the year 2016 is merely a label and should not be included; therefore, you would ignore that error message
Function Basics
An Excel function is a predefined computation that simplifies creating a formula that performs a complex calculation.
A. Inserting a Function
· Demonstrate that to insert a function by typing, first type an equal sign, and then begin typing the function name. Formula AutoComplete displays a list of functions and defined names that match letters as you type a formula
· To display the Insert Function dialog box, click Insert Function (located between the Name Box and the Formula Bar) or click Insert Function in the Function Library group on the Formulas tab.
v Teaching Tips: Do not use a function for a basic mathematical expression, use =A3*B3; not =SUM(A3*B3)
· Teaching Tips: If you enter a function and #NAME? displays in the cell, you might have mistyped the function name. To avoid this problem, select the function name from the Formula AutoComplete list as you type the function name, or use the Insert Function dialog box. You can type a function name in lowercase letters. If you type the name correctly, Excel converts the name to all capital letters when you press Enter, indicating that you spelled the function name correctly.
B. Inserting Basic Math and Statistics Functions
· Show how the SUM function totals values in two or more cells and displays the result in the cell containing the function. This function is more efficient to create when you need to add the values contained in three or more cells.
· Demonstrate that Excel provides two functions to calculate central tendency: AVERAGE and MEDIAN. The AVERAGE function calculates the arithmetic mean, or average, for the values in a range of cells. The MEDIAN function finds the midpoint value, which is the value that one half of the data set is above or below. The median is particularly useful because extreme values often influence arithmetic mean calculated by the AVERAGE function.
· Show how the MIN function analyzes an argument list to determine the lowest value, such as the lowest score on a test.
· Demonstrate how the COUNT function tallies the number of cells in a range that contain values you can use in calculations, such as numerical and date data, but excludes blank cells or text entries from the tally. The COUNTBLANK function tallies the number of cells in a range that are blank. The COUNTA function tallies the number of cells in a range that are not blank, that is, cells that contain data, whether a value, text, or a formula.
v Teaching Tips: In this book, the function syntax lines are highlighted. Brackets [ ] indicate optional arguments; however, do not actually type the brackets when you enter the argument
v Teaching Tips: When you select a range of cells containing values, by default Excel displays the average, count, and sum of those values on the status bar. You can customize the status bar to show other selection statistics, such as the minimum and maximum values for a selected range. To display or hide particular selection statistics, right-click the status bar and select the statistic.
v Teaching Tips: Excel 2013 contains a new feature called Quick Analysis, which is a set of analytical tools you can use to apply formatting, create charts or tables, and insert basic functions. When you select a range of data, the Quick Analysis button displays in the bottom-right corner of the selected range. Click the Quick Analysis button to display the Quick Analysis gallery and select the analytical tool to meet your needs.
v Teaching Tips: When you click Decrease Decimal in the Number group to display fewer or no digits after a decimal point, Excel still stores the original value’s decimal places so that those digits can be used in calculations. The ROUND function changes the stored value to its rounded state.
· A nested function occurs when one function is embedded as an argument within another function. Each function has its own set of arguments that must be included
C. Using Date Functions
· Demonstrate that Excel treats dates as serial numbers; you can perform calculations using dates.
· The TODAY function displays the current date, such as 6/14/2016, in a cell.
v Teaching Tips: Excel updates the function results when you open or print the workbook. The TODAY() function does not require arguments, but you must include the parentheses. If you omit the parentheses, Excel displays #NAME? in the cell with a green triangle in the top-left corner of the cell.
· The NOW() function uses the computer’s clock to display the date and military time, such as 6/14/2016 15:30, that you last opened the workbook. (Military time expresses time on a 24-hour period where 1:00 is 1 a.m. and 13:00 is 1 p.m.)
Logical, Lookup, and Financial Functions
Lookup and reference functions are useful when you need to look up a value in a list to identify the applicable value. Financial functions are useful to anyone who plans to take out a loan or invest money.
A. Determining Results with the IF Function
· Demonstrate the most common logical function which is the IF function, which returns one value when a condition is met or is true and returns another value when the condition is not met or is false.
· The IF function has three arguments: (1) a condition that is tested to determine if it is either true or false (the logical test), (2) the resulting value if the condition is true, and (3) the resulting value if the condition is false.
v Teaching Tips: When you use text in a formula or function, you must enclose the text in quotation marks. However, do not use quotation marks around formulas, cell references, or values.
v Teaching Tips: You can nest functions in the logical test, value_if_true, and value_if_false arguments of the IF function. When you nest functions as arguments, make sure the nested function contains the required arguments for it to work and that you nest the function in the correct argument to calculate accurate results.
B. Using Lookup Functions
· You can use lookup and reference functions to look up values to perform calculations or display results.
· Explain the layout of a lookup table which is a range containing a table of values or text that can be retrieved. The table should contain at least two rows and two columns, not including headings.
· The VLOOKUP function accepts a value, looks the value up in a vertical lookup table, and returns a result. Use VLOOKUP to search for exact matches or for the nearest value that is less than or equal to the search value, such as assigning a B grade for an 87% class average.
· The VLOOKUP function has the following three required arguments and one optional argument: (1) lookup_value, (2) table_array, (3) col_index_number, and (4) range_lookup.
· The lookup value is the reference of the cell that contains the value to look up. The table array is the range that contains the lookup table. The table array range must be absolute and cannot include column labels for the lookup table. The column index number is the column number in the lookup table that contains the return values.
v Teaching Tips: We know to avoid using values in formulas because the input values in a worksheet cell might change. However, the value 2 is used in the col_index_number argument of the VLOOKUP function. The 2 refers to a particular column within the lookup table and is an acceptable use of a number within a formula.
· You can design a lookup table horizontally where the first row contains the values for the basis of the lookup or the breakpoints, and additional rows contain data to be retrieved. With a horizontal lookup table, use the HLOOKUP function. The syntax is almost the same as the syntax for the VLOOKUP function, except the third argument is row_index_number instead of col_index_number
C. Calculating Payments with the PMT Function
· Demonstrate the PMT function which calculates payments for a loan with a fixed amount at a fixed periodic rate for a fixed time period. The PMT function uses three required arguments and up to two optional arguments: (1) rate, (2) nper, (3) pv, (4) fv, and (5) type.
Range Names
To simplify entering ranges in formulas, range names can be used. A range name is a word or string of characters assigned to one or more cells.
A. Creating and Maintaining Range Names
· Each range name in the same workbook must be unique. A range name can contain up to 255 characters, but it must begin with a letter or an underscore.
· Use the Name Manager dialog box to edit, delete, and create range names.
B. Using Range Names in Formulas
· Demonstrate how you can use range names in formulas instead of cell references.
v Teaching Tips: Benefit of using range names is that they are absolute references, which helps ensure accuracy in your calculations
v Teaching Tips: When you paste range names, the list will overwrite any existing data in a worksheet, so consider pasting the list in a separate worksheet. If you add, edit, or delete range names, the list does not update automatically. To keep the list current, you would need to paste the list again.
ONLINE CHAPTER REVIEW
To find an online chapter review to help your students practice for tests, visit the Companion Web site at http://www.pearsonhighered.com/exploring/.
ADDITIONAL WEB RESOURCES
1. New functions in Excel 2013: http://office.microsoft.com/en-us/excel-help/new-functions-in-excel-2013-HA103980604.aspx
2. 51 New Worksheet Functions in Excel 2013: http://excelblogger.com/newfunctions-excel-2013/
3. For the 12 Days of Christmas, 12 New (and Useful) Excel 2013 Features: http://www3.cfo.com/article/2012/12/spreadsheets_excel-2013-new-features-accounting-pivot-tables-tweets
4. Using the WebService() function in Excel 2013: http://cwebbbi.wordpress.com/2012/07/31/using-the-webservice-function-in-excel-2013/
5. Excel 2013 Tips and Tricks: http://www.youtube.com/course?list=ECEBD7C5C90C5A2B78
PROJECTS AND EXERCISES
| Data file | Save As | |
| Hands-On Exercise 1 | e02h1Loans | e02h1Loans_LastFirst |
| Hands-On Exercise 2 | e02h1Loans_LastFirst | e02h2Loans_LastFirst |
| Hands-On Exercise 3 | e02h2Loans_LastFirst | e02h3Loans_LastFirst |
| Hands-On Exercise 4 | e02h3Loans_LastFirst | e02h4Loans_LastFirst |
| Practice Exercise 1 | e02p1Flights | e02p1Flights_LastFirst |
| Practice Exercise 2 | e02p2Salary | e02p2Salary_LastFirst |
| Practice Exercise 3 | e02p3CarLoan | e02p3CarLoan_LastFirst. |
| Mid-Level Exercise 1 | e02m1Payroll | e02m1Payroll_LastFirst |
| Mid-Level Exercise 2 | Blank workbook | e02m2Loan_LastFirst |
| Mid-Level Exercise 3 | e02m3Grades | e02m3Grades_LastFirst |
| Mid-Level Exercise 4 (collaboration) | Blank document. | e02t1_LastFirst. |
| BYC Research | Blank workbook | e02b2Sports_LastFirst |
| BYC Disaster Recovery | e02b3ParkCity | e02b3ParkCity_LastFirst |
| BYC Soft Skills | Blank workbook | e02b4Interview_LastFirst |
| Capstone | e02c1Gym | e02c1Gym_LastFirst |
CHAPTER REVIEW/ANSWERS TO END OF CHAPTER MATERIAL
Key Terms Matching Answer Key
1. Syntax (R) is the set of rules that governs the structure and components for properly entering a
function. p. 456
2. The TODAY function (S) displays the current date. p. 463
3. Absolute cell reference (A) indicates a cell’s specific location; the cell reference does not change when you copy the formula. p. 448
4. Circular reference (D) occurs when a formula directly or indirectly refers to itself. p. 450
5. An Argument (B) is an input, such as a cell reference or value, needed to complete a function. p. 456
6. The MAX function (I) identifies the highest value in a range. p. 461
7. The COUNT function (E) tallies the number of cells in a range that contain values. p. 461
8. The VLOOKUP function (T) looks up a value in a vertical lookup table and returns a related result from the lookup table. p. 474
9. A lookup table (H) is a range that contains data for the basis of the lookup and data to be retrieved. p. 474
10. An AVERAGE function (C) calculates the arithmetic mean, or average, of values in a range. p. 460
11. MEDIAN function (J) identifies the midpoint value in a set of values. p. 460
12. The NOW function (M) displays the current date and time. p. 463
13. The IF function (F) evaluates a condition and returns one value if the condition is true and a different value if the condition is false. p. 471
14. The SUM function (Q) calculates the total of values contained in two or more cells. p. 458
15. A PMT function (N) calculates the periodic payment for a loan with a fixed interest rate and fixed term. p. 477
16. The relative cell reference (P) indicates a cell’s location from the cell containing the formula; the cell reference changes when the formula is copied. p. 448
17. A mixed cell reference (L) contains both an absolute and a relative cell reference in a formula; the absolute part does not change but the relative part does when you copy the formula. p. 450
18. A range name (O) is a word or string of characters that represents one or more cells. p. 482
19. The logical test (G) is an expression that evaluates to true or false. p. 472
20. A MIN function (K) displays the lowest value in a range. p. 461
Multiple Choice Answer Key
1. If cell D15 contains the formula =$C$5*D$15, what is the D15 in the formula?
(c) Circular reference
2. What function would most appropriately accomplish the same thing as =(B5+C5+D5+E5+F5)/5?
(b) =AVERAGE(B5:F5)
3. When you start =AV, what displays a list of functions and defined names?
(b) Formula AutoComplete
4. A formula containing the entry =$B3 is copied to a cell one column to the right and two rows down. How will the entry appear in its new location?
(d) =$B5
5. Cell B10 contains a date, such as 1/1/2016. Which formula will determine how many days are between that date and the current date, given that the cell containing the formula is formatted with Number Format?
(c) =TODAY()-B10
6. Given that cells A1, A2, and A3 contain values 2, 3, and 10, respectively, and B6, C6, and D6 contain values 10, 20, and 30, respectively, what value will be returned by the function =IF(B6>A3,C6*A1,D6*A2)?
(d) 90
7. Given the function =VLOOKUP(C6,$D$12:$F$18,3), the entries in:
(a) Range D12:D18 are in ascending order.
8. The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C5?
(b) Periodic interest rate
9. Which of the following is not an appropriate use of the SUM function?
(d) =SUM(D15-C15)
10. Which of the following is not an acceptable range name?
(c) Goal for 2016.
No comments:
Post a Comment