New Perspectives on Microsoft Excel 2016 Instructor’s Manual 1 of 5
Microsoft Excel 2016
Module 8: Working with Advanced Functions
A Guide to this Instructor’s Manual:
We have designed this Instructor’s Manual to supplement and enhance your teaching experience through
classroom activities and a cohesive module summary.
In addition to this Instructor’s Manual, our Instructor’s Resources also contains PowerPoint Presentations,
Test Banks, and other supplements to aid in your teaching experience.
Table of Contents
Module Objectives
1
Working with Logical Functions
1
Using LOOKUP Functions
Using the IFERROR Function
Applying Conditional Formatting
Using Functions to Summarize Data Conditionally
End of Module Material
Module Objectives
Students will have mastered the material in this module when they can:
• Use the IF function
• Use the AND function
• Use the HLOOKUP function
• Use the IFERROR function
Working with Logical Functions
LECTURE NOTES
• Demonstrate how to insert calculated columns in an Excel table.
TEACHER TIP
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 2 of 5
Remind students that whenever they enter a formula into an empty table column, Excel automatically fills
CLASSROOM ACTIVITIES
Class Discussion: In what type of situations would you consider using the IF function? Inn what type of
situations would you consider using the AND function?
Quick Quiz:
Using Structured References to Create Formulas in Excel Tables
LECTURE NOTES
• Demonstrate how to use structured references to create formulas in Excel tables.
CLASSROOM ACTIVITIES
Class Discussion: What is a structured reference? How does this make the formula easier to create and
Creating Nested Ifs
LECTURE NOTES
• Show how to enter nested IFs.
• Discuss finding and fixing errors in formulas.
TEACHER TIP
Remind students that an IF function can choose between only two outcomes. However, a nested IF
function can choose from among three or more outcomes.
CLASSROOM ACTIVITIES
Class Discussion: What is the difference between a syntax error and a logic error? (Answer: A syntax
error is an error in a statement that violates the rules of Excel such as unmatched parentheses. A
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 3 of 5
Using LOOKUP Functions
LECTURE NOTES
• Show how to use the VLOOKUP function to find an exact match.
TEACHER TIP
When a lookup table is used with a range of values, the compare values must be sorted in alphabetical order
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False. The VLOOKUP function searches vertically down the lookup table. (Answer:
True)
2. True/False. The VLOOKUP function is used to compare values stored in the first column of
the lookup table. (Answer: False)
Using the IFERROR Function
LECTURE NOTES
• Show how to check for data entry errors using the IFERROR function.
TEACHER TIP
Review Figure 8-33 with students and discuss the most common Excel error values.
CLASSROOM ACTIVITIES
Quick Quiz:
1. _______ indicate(s) that some element in a formula or a cell referenced in a formula is
preventing Excel from returning a calculated value. (Answer: Error values)
Applying Conditional Formatting
LECTURE NOTES
• Show how to highlight duplicate values.
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 4 of 5
• Demonstrate how to use the conditional formatting rules manager.
TEACHER TIP
To avoid the possibility of invalid data being entered into a table, you can use conditional formatting and
CLASSROOM ACTIVITIES
Quick Quiz:
1. True/False. Conditional formatting does not change a cell’s formatting when its contents
match a specified condition. (Answer: False)
Using Functions to Summarize Data Conditionally
LECTURE NOTES
• Show how to summarize data using the COUNTIF, SUMIF, and AVERAGEIF functions.
TEACHER TIP
Make sure students understand the differences among the functions and when each should be used. Help
CLASSROOM ACTIVITIES
1. Critical Thinking: What are the differences among the COUNT, SUM, and AVERAGE functions?
(Answer: The COUNT function tallies the number of data values in a range, the SUM function adds
the values in a range, and the AVERAGE function calculates the average of the values in a range.)
How do these three functions differ from the COUNTIF, SUMIF, and AVERAGEIF functions?
End of Module Material
• Review Assignments: Review Assignments provide students with additional practice of the skills they
learned in the module using the same module case, with which they are already familiar. These
New Perspectives on Microsoft Excel 2016 Instructor’s Manual 5 of 5
The Case Problems provide further hands-on assessment of the skills and topics presented in the
module, but with new case scenarios. There are five types of Case Problems:
• Apply. In this type of Case Problem, students apply the skills that they have learned in the module to
solve a new problem.