Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

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

06/11/2020 Client: arwaabdullah Deadline: 3 days

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

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Peter O.
Coursework Help Online
A+GRADE HELPER
Writer Writer Name Offer Chat
Peter O.

ONLINE

Peter O.

Hello, I can assist you in writing attractive and compelling content on ganja and its movement globally. I will provide with valuable, informative content that you will appreciate. The content will surely hit your target audience. I will provide you with the work that will be according to the needs of the targeted audience and Google’s requirement.

$55 Chat With Writer
Coursework Help Online

ONLINE

Coursework Help Online

Hey, I have gone through your job posting and become very much interested in working with you.I can deliver professional content as per your requirements. I am a multi-skilled person with sound proficiency in the English language for being a native writer who worked on several similar projects of content writing and can deliver quality content to tight deadlines. I am available for both online and offline writing jobs with the promise of offering an incredibly responsive and supreme level of customer service. Thanks!

$55 Chat With Writer
A+GRADE HELPER

ONLINE

A+GRADE HELPER

Greetings! I’m very much interested to work on this project. I have read the details properly. I am a Professional Writer with over 5 years of experience, therefore, I can easily do this job. I will also provide you with TURNITIN PLAGIARISM REPORT. You can message me to discuss the detail. Why me? My goal is to offer services to you that are profitable. I don’t want you to place an order once and that’s it. For me to be successful, I need you to come back and order again. Give me the opportunity to work on your project. I wish to build a long-term relationship with you. We can have further discussion in chat. Thanks!

$55 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

This is due tomorrow morning - Bob neugebauer travel roseville michigan - Tlid2004 load and unload goods/cargo - Rates and ratios year 8 - How many different 4 letter radio station - Http mondeca com index php en any place en - Nathaniel hawthorne interesting facts - Chemistry book grade 10 - Public budgeting - Children's milestones chart 0 16 - Neurofascial process - Islam and christianity venn diagram - Aps integrated leadership system - Galls street thunder siren - Research reeee - Youyang airport lighting equipment - From wealth to power fareed zakaria pdf - La salle benilde tuition fee - +91-8306951337 love marriage specialist astrologer IN Navi Mumbai - Fonderia di torino case - Client identity services dublin - Exp 105 week 3 chapter quiz - 2x 2 9x 4 0 quadratic formula - Derivative of tanh 1x - Aronson wilson & sommers social psychology 10th edition - How was a computer made - Senior project board examples - Claritas my best segments zip code - Financial accounting chapter 4 comprehensive problem 1 - Sales pitch for sponsorship - Criminal Justice - Ueda akinari tales of moonlight and rain pdf - Circular motion practice problems worksheet - Interpretive simulations hr management tips - The probability that a cellular phone company kiosk - Vital Signs - Small Groups - IOP 460: Organizational Cultures, Discussion Response (2) - 4 scales of measurement - The cranes by peter meinke meaning - Practical-Pra - Cmit 350 network infrastructure design - John ortberg speaking fee - Matlab state space simulation - Progress notes - American civil war a streetcar named desire - Title of the organization - Principles of advocacy in nursing - What od interventions would you suggest in this case - JOHANNESBURG ABORTION CLINIC 0717852514 ABORTION PILLS FOR SALE IN IN WELKOM, SASOLBURG, EASTERN CAPE, MAMELODI, SHOSHANGUVE, ARCADIA, - Fung sin hardware & supply - Website and web service difference - Solitaire company's fixed budget performance - Systemic formulation template - 6-8 pages paper - Csirt team roles and responsibilities - Abb 11kv switchgear specification - Sample lab report chemistry - Crucified skinhead tattoo meaning - The most famous work of egyptian literature is - Strategy plans and budgets are unrelated to one another - Cpap in neonates ppt - Practical connection assignment 500 WORD ( due in 4 hours MANDATORY ) NO PLAGIARISIM ). - Short term goals for nurse practitioner student - How are headlands created - What is the midpoint of the class 7-11 ? - Copper sun summary chapter 1 - Azimuth marine port hedland - PHIL 105: Critical Thinking - Value Creation in Organizations - Outline & References - Vcaa 2011 biology exam 2 answers - Disaster Management - Maternity exemption certificate optician - Marketing the core 8th edition pdf free download - Box gutter rainwater head - Unit 8 Data Analysis and Application - The Sociological Imagination and Social Causes of Suicide - Discusision and apper - Pros and cons of accounting regulation - Morden golden glow elder - Representative - Pearl's peril hire flight crew - Correction System W1 - Sample learning plan template - Create a scenario summary report excel 2013 - Chemical reactions and equations lab - Natural hygiene food combining chart - Round 91 284 to the nearest hundred thousands - Wolfgang amadeus mozart marcha turca jose albero francés - Dove body wash 34 oz walmart - Assignment 1: Procedural Law and the Bill of Rights - Introduction To Criminology Course Help - Ohmic and non ohmic behaviour - How to pronounce russian alphabet - How to increase working capital in capsim - Deakin exercise and sport science - National qualifications framework nz - Week 7 essay phi - Postal codes new zealand