Lookup tables can be constructed for exact match or approximate match lookups.

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

© 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.

‹#›

7

Using the IF Function

A logical function that evaluates a single condition and results in only one value

Returns one value if the condition is true and another value if the condition is false

Syntax:

IF(logical_test, value_if_true, value_if_false)

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.

‹#›

8

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.

‹#›

9

Using the AND Function

A logical function that tests two or more conditions (up to 255) and determines whether all conditions are true

Returns the value TRUE if all logical conditions are true and the value FALSE if any or all logical conditions are false

Syntax:

AND(logical1[,logical2]...)

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.

‹#›

10

Using the OR Function

A logical function that returns a TRUE value if any of the logical conditions (up to 255) are true and a FALSE value if all the logical conditions are false

Syntax:

OR(logical1[,logical2]...)

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.

‹#›

11

Replace specific cell or range address with a structured reference, the actual table name, or a column header

A formula that includes a structured reference can be fully qualified or unqualified

Using Structured References to Create Formulas in Excel Tables

© 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.

‹#›

12

Using Structured References to Create Formulas in Excel Tables

© 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.

‹#›

13

Visual Overview: Nested IFs and Lookup Tables

© 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.

‹#›

14

To allow for three or more outcomes

One IF function is placed inside another IF function to test an additional condition

More than one IF function can be nested

Creating Nested IFs

© 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.

‹#›

15

Creating Nested IFs

© 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.

‹#›

16

The following formula and flowchart convey the same nested IF function =IF([Years of Service]>=10,500, IF([Years of Service]>=5, 250, 100))

Creating Nested IFs

© 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.

‹#›

17

Lookup functions allow you to use tables of data to find values in a table and insert them in another worksheet location

Both the VLOOKUP and HLOOKUP functions are used to return a value from a lookup table

The VLOOKUP function always searches for a value in the first column of the lookup table

The HLOOKUP function always searches for a value in the first row of the lookup table

Using LOOKUP 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.

‹#›

18

Lookup tables can be constructed as either exact match or approximate match lookups

Exact match lookup occurs when the lookup value must match one of the values in the first column (or row) of the lookup table

An approximate match lookup occurs when the lookup value is found within a range of numbers in the first column (or row) of the lookup table

Using LOOKUP 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.

‹#›

19

Using the VLOOKUP Function to Find an Exact Match

Searches vertically down the first column of the lookup table

Syntax:

VLOOKUP(lookup_value,table_array,col_index_num[range_lookup])

Using LOOKUP 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.

‹#›

20

Using LOOKUP 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.

‹#›

21

Using the VLOOKUP Function to Find an Approximate Match

Returns a value based on an approximate match lookup in the first column of the table

The values in the first column or row of a lookup table can represent a range of values

Quantity discounts, shipping charges, and income tax rates are a few examples of approximate match lookups

Using LOOKUP 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.

‹#›

22

Using the HLOOKUP Function to Find an Exact Match

Searches horizontally across top row of table and retrieves the value in the column you specify

Use when comparison values are located in the first row of the lookup table and you want to look down a specified number of rows to find the data to enter in another cell

Syntax:

HLOOKUP(lookup_value,table_array,row_index_num[,range_lookup])

Using LOOKUP 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.

‹#›

23

Using the HLOOKUP Function to Find an Exact Match (continued)

Major difference between HLOOKUP and VLOOKUP functions is the way lookup tables are organized

Using LOOKUP 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.

‹#›

24

Error values

Indicate that an element in a formula or a cell referenced in a formula is preventing Excel from returning a calculated value

Begin with a number sign (#) followed by an error name that indicates the type of error

Using the IFERROR Function

© 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.

‹#›

25

Displays a more descriptive message that helps users fix the problem

Can determine if a cell contains an error value and then display the message you choose rather than the default error value

Use the IFERROR function to find and handle formula errors

Syntax:

IFERROR(expression,valueIfError)

Using the IFERROR Function

© 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.

‹#›

26

Using the IFERROR Function

© 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.

‹#›

27

Visual Overview: Conditional Formatting and 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.

‹#›

28

Changes a cell’s formatting when its contents match a specified condition

Can be used to:

Highlight cells based on their values

Add data bars that graph relative values in a range

Highlight duplicate values in a column of data

Applying Conditional Formatting

© 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.

‹#›

29

Highlighting Duplicate Values

Excel is often used to manage lists of data, such as:

Employee information

Inventory

Phone numbers

Some of the data is unique for each record, such as an employee ID or a social security number

One way to identify unintended duplicate entries is to use conditional formatting to highlight duplicate values in a range with a font and/or fill color

Applying Conditional Formatting

© 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.

‹#›

30

Applying Conditional Formatting

© 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.

‹#›

31

Using the Conditional Formatting Rules Manager

A conditional formatting rule specifies:

Type of condition

Type of formatting when that condition occurs

Cell or range the formatting is applied to

Use Conditional Formatting Rules Manager dialog box to edit existing conditional formatting rules

Applying Conditional Formatting

© 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.

‹#›

32

Use COUNTIF, SUMIF, and AVERAGEIF functions to calculate a conditional count, sum, or average using only cells that meet a particular condition

Using the COUNTIF Function

Calculates the number of cells in a range that match specified criteria

Sometimes referred to as a conditional count

Syntax:

COUNTIF(range, criteria)

Using Functions to Summarize Data Conditionally

© 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.

‹#›

33

Using Functions to Summarize Data Conditionally

© 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.

‹#›

34

Using the SUMIF Function

Adds values in a range that meet your criteria

Also called a conditional sum

Syntax:

SUMIF(range, criteria[, sum_range])

Using the AVERAGEIF Function

Similar to SUMIF function

Calculates the average of values in a range that meet criteria you specify

Syntax:

AVERAGEIF(range, criteria[, average_range])

Using Functions to Summarize Data Conditionally

© 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.

‹#›

35