Graphics & Visualization Chapter 3 Homework Functions are organized in the Function Library group

Unlock access to all the studying documents.

View Full Document

New Perspectives on Microsoft Excel 2016 Instructor’s Manual 1 of 8

Microsoft Excel 2016

Module 3:

Performing Calculations with Formulas and 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.

Table of Contents

Module Objectives

Making Workbooks User-Friendly

Using Excel Functions

Performing What-If Analyses

Interpreting Error Values

End of Module Material

AutoFilling Formulas and Data

Module Objectives

Students will have mastered the material in this module when they can:

• Document formulas and data values

• Explore function syntax

• Use relative and absolute cell references

• Use the Quick Analysis tool

Making Workbooks User-Friendly

New Perspectives on Microsoft Excel 2016 Instructor’s Manual 2 of 8

LECTURE NOTES

• Explain how to document formulas

CLASSROOM ACTIVITIES

Classroom Discussion: What are the benefits of documenting the contents of a workbook? (Answer:

Helps to avoid errors and confusion; helpful to explain the formulas and terms used in the calculations;

serve as a check that equations are accurate; anyone who needs to enter data in the workbook or

Using Excel Functions

LECTURE NOTES

• Review function categories

• Discuss function syntax

TEACHER TIP

A function is a predefined, or built-in, formula for a commonly used calculation. Each Excel function has a

name and syntax. The syntax specifies the order in which you must enter the different parts of the function

and the location in which you must insert commas, parentheses, and other punctuation. Arguments are

numbers, text, or cell references used by the function to calculate a value. Some arguments are optional.

CLASSROOM ACTIVITIES

Class Discussion: What is the difference between the COUNT and the COUNTA functions? (Answer:

The COUNT function tallies how many cells in a range contain numbers or dates, stored as numeric

New Perspectives on Microsoft Excel 2016 Instructor’s Manual 3 of 8

values; the COUNTA function tallies how many cells contain entries, whether they are numbers, dates,

or text.)

Quick Quiz:

1. True/False: Functions are organized in the Function Library group. (Answer: True)

2. True/False: To add a collection of numbers, use the COUNT function. (Answer: False)

LAB ACTIVITIES

Divide students into groups of two or three. Assign each group one of the functions found in the

Function Library group on the Formulas tab. Ask each group to use the help available to research their

Performing What-If Analyses

LECTURE NOTES

• Explain how a what-if analysis can be used

CLASSROOM ACTIVITIES

Class Discussion: How would you perform a what-if analysis by trial and error? (Answer: Change the

value of the worksheet cell (input cell); observe its impact on one or more calculated cells (the results

cells); repeat until the desired results are achieved.) How would you perform a what-if analysis using

Goal Seek? (Answer: On the Data tab, in the Data Tools group, click the What-If Analysis button, and

Interpreting Error Values

LECTURE NOTES

• Discuss how to interpret error values

New Perspectives on Microsoft Excel 2016 Instructor’s Manual 4 of 8

CLASSROOM ACTIVITIES

Class Discussion: Review Figure 3-15. The error value messages themselves are not very descriptive or

helpful. How would you help locate an error? (Answer: Point to the error indicator and read the

ScreenTip that appears with more information about the source of the error.)

Quick Quiz:

AutoFilling Formulas and Data

LECTURE NOTES

• Demonstrate how to use the AutoFill feature

TEACHER TIP

The fill handle is a small black square located in the lower-right corner of a selected cell or range. When

you drag the fill handle, Excel automatically fills in the formulas and formats used in the selected cells. This

technique is also known as AutoFill. The same rules for relative, absolute, and mixed references apply for

CLASSROOM ACTIVITIES

Class Discussion: What is a series? How do you create a series using AutoFill? (Answer: A series is

number of objects or events arranged or coming one after the other in succession—numbers, dates, or

text based on a pattern. To create a series of numbers, you enter the initial values in the series in a

selected range and then use AutoFill.)

Quick Quiz:

Exploring Cell References

LECTURE NOTES

• Discuss and demonstrate relative cell references

New Perspectives on Microsoft Excel 2016 Instructor’s Manual 5 of 8

TEACHER TIP

Many students have a difficult time knowing when and how to use relative, absolute, and mixed references.

Mastering this skill will help them to create useful worksheets quickly that also yield the correct results.

Applying this concept incorrectly can cause a great deal of error. Remind them it is always important to

check their results to see if they make sense. Spend time covering each reference type and when each

would be used.

A mixed reference is a cell reference that contains both relative and absolute references.

You can switch among absolute, relative, and mixed references in a formula when in editing mode or on the

formula bar by selecting the cell reference in your formula and then pressing the F4 key repeatedly to

toggle through the reference options.

CLASSROOM ACTIVITIES

Classroom Discussion: What are the differences among relative, absolute, and mixed references?

(Answer: To enter a relative reference, type the cell reference as it appears in the worksheet. For

example, enter B2 for cell B2. To enter an absolute reference, type $ (a dollar sign) before both the

row and column references. For example, enter $B$2. To enter a mixed reference, type $ before

either the row or column reference. For example, enter $B2 or B$2.Part of writing effective

Quick Quiz:

1. Use the _______ symbol to make a cell reference absolute. (Answer: C)

A. asterisk (*)

B. number (#)

C. dollar sign ($)

D. equal sign (=)

New Perspectives on Microsoft Excel 2016 Instructor’s Manual 6 of 8

Summarizing Data with the Quick Analysis Tool

LECTURE NOTES

• Demonstrate how to use the Quick Analysis Tool to generate columns and rows of summary

statistics and formals

CLASSROOM ACTIVITIES

Classroom Discussion: For what types of information would you use the Quick Analysis tool? Why

Working with Dates and Date Functions

LECTURE NOTES

• Demonstrate how to enter Date functions

• Show how to display the current date with the TODAY function

• Explain how to find the next weekday with the WORKDAY function

TEACHER TIP

Creative Thinking Activity: If you want to create a date value for the date represented by the year,

month, and day arguments, you use which function? (Answer: DATE) If you want to create a

Using Lookup Functions

LECTURE NOTES

• Demonstrate how to use a lookup function

• Explain how to find an exact match with the VLOOKUP function

New Perspectives on Microsoft Excel 2016 Instructor’s Manual 7 of 8

values in the first column of the lookup table. An exact match lookup occurs when the lookup value

falls within a range of numbers in the first column of the lookup table.)

Quick Quiz:

Working with Logical Functions

LECTURE NOTES

• Demonstrate how to use and insert the IF function

• Review comparison operators

TEACHER TIP

Review the basic concepts of a logical function with the students. A function that determines whether a

condition is true or false is called a logical function. Excel supports several logical functions such as AND,

FALSE, IF, NOT, OR, and TRUE. A very common function is the IF function, which uses a logical test to

CLASSROOM ACTIVITIES

Class Discussion: What is a logical function? (Answer: A logical function is a function that works with

values that are either true or false.) What does the following function mean? =IF(A1=B1, 100, 50

(Answer: The formula compares the values in cells A1 and B1. If they’re equal, a value of 100 will show;

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

assignments are designed as straight practice and do not include anything of an exploratory nature.

New Perspectives on Microsoft Excel 2016 Instructor’s Manual 8 of 8

• Apply. In this type of Case Problem, students apply the skills that they have learned in the module to

solve a new problem.

• Create. In a Create Case Problem, students are either shown the end result (such as a finished Word

document) and asked to create the document based on the figure provided, or, students are asked to

create something from scratch in a more free-form manner.