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