Search This Blog(textbook name or author as the keywords)You can cantact me by the Contact Form

9/4/14

Exploring: Microsoft Excel 2013, Comprehensive Mary Anne Poatsy solutions manual and test bank

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)

clip_image001

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)

clip_image002

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

 
EXPLORING MICROSOFT® OFFICE 2013, VOLUME 1

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

Prepared Exam-Chap Annotated Sol.

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 functionDisplays 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

Linkwithin

Related Posts Plugin for WordPress, Blogger...