hw
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