New Perspectives on Excel 2016

Module 8: Working with Advanced Functions

‹#›

Use the IF function

Use the AND function

Use the OR function

Use structured references in formulas

Nest the IF function

Use the VLOOKUP function

Objectives

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

‹#›

2

Use the HLOOKUP function

Use the IFERROR function

Use conditional formatting to highlight duplicate values

Summarize data using the COUNTIF, SUMIF, and AVERAGEIF functions

Objectives

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

‹#›

3

Visual Overview: Logical Functions

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

‹#›

4

Logical functions (IF, AND, and OR) determine whether a condition is true or false

Conditions use a comparison operator (<, <=, =, <>, >, or >=) to compare two values

Combine two or more functions in one formula to create more complex conditions

Working with Logical Functions

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

‹#›

5

Inserting Calculated Columns in an Excel Table

Entering a formula in one cell of a column automatically copies the formula to all cells in that column

To modify the formula in a calculated column:

Edit the formula in any cell in the column

Formulas in all cells in the column are modified

To edit only one cell in a calculated column:

Enter a value or a formula that is different from all others in that column

Working with Logical Functions

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

‹#›

6

To effectively communicate a table’s function, keep the following guidelines in mind when creating fields in an Excel table:

Create fields that require the least maintenance

Store smallest unit of data possible in a field

Apply a text format to fields with numerical text data

Working with Logical Functions