Instructor for this course
more

Excel expert David Ringstrom, CPA, begins with a brief discussion of the IF function and then takes you step-by-step beyond the basics. David explains what can go awry with the IF function and ways to improve the integrity of decision-making formulas. David shares alternatives to the IF function, including IFS, CHOOSE, VLOOKUP, SUMIF, and several other worksheet functions. The presentation also explores techniques useful in verifying that formulas such as nested IF statements are working properly.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He’ll draw your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

Topics covered:

  • Facilitating decision making within Excel formulas by way of the IF function.
  • Testing for two or more alternate conditions by way of the OR function.
  • Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
  • Auditing portions of a formula by using the F9 key to temporarily convert part of a formula to a value.
  • Stepping through formulas in slow motion with the Evaluate Formulas feature.
  • Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
  • Testing for a single condition among two or more that returns TRUE by way of Excel’s XOR function.
  • Employing the ISERROR function to determine if a formula returns a # sign error as the basis for triggering alternate calculations.
  • Mastering the IFERROR function to display alternate values in lieu of a # sign error.
  • Streamlining the decision-making process with the IFS function in Office 365.
  • Discovering the range of IS functions that can be used within IF statements to test for various conditions within a worksheet.
  • Understanding when you might wish to use ISERROR or ISNA instead of IFERROR.

Learning Objectives

  • Identify the worksheet function that enables you to determine whether at least one logical test returns TRUE
  • Discover the purpose of Excel's IFERROR worksheet function.
  • Identify what the SUMIFS function returns if a match cannot be found.
Last updated/reviewed: November 7, 2019

Included In Certifications

This course is included in the following Certification Programs:

17 CoursesCertified Excel Modeler

  1. Excel Efficiency: VLOOKUP
  2. Excel Efficiency: Logic Functions
  3. Excel Efficiency: Filtering and Formatting Data
  4. Excel Efficiency: Intermediate Pivot Tables
  5. Excel Efficiency: Auditing Spreadsheets
  6. Excel Efficiency: Minimizing Worksheet Errors
  7. Excel Efficiency: Workbook Links
  8. Excel Efficiency: What-If Analysis
  9. Excel Efficiency: Quick and Easy Financial Statements in Excel
  10. Excel Efficiency: Budget Spreadsheets
  11. Hands-On Excel: Waterfall Calculations
  12. Excel Efficiency: Table Feature
  13. Excel Efficiency: Intro to Macros Part 1
  14. Excel Efficiency: Intro to Macros Part 2
  15. Excel Efficiency: Excel Chart Speed Tips
  16. Excel Efficiency: Taming Large Spreadsheets
  17. Excel Efficiency: Internal Controls

8 Reviews (29 ratings)Reviews

5
Anonymous Author
Good explanation of some of the newer Excel functions like IFERR and XOR. Some of the other functions like SUMIF are also covered in this trainer's other courses.
5
Member's Profile
Excellent discussion of all the ins and outs of logical expressions. Especially liked the discussions around how to use columns as ranges in sumifs and vlookups.
5
Anonymous Author
This was a very helpful course with a pragmatic application. Not only introduced the concepts, but gave useful examples of how to apply the formulas.
5
Member's Profile
Great examples and detailed slides make this type of instruction my hands down favorite.
4
Anonymous Author
Great course. First real understanding of logic statements and not being overwhelmed.
5
Member's Profile
Awesome course. A deep dive into excel formulas extremely useful for data analysis.
4
Anonymous Author
Once again, a lot of valuable material delivered in a clear and concise manner.
4
Anonymous Author
This is very helpful. I thought I knew everything about the IF function.

Prerequisites

Course Complexity: Intermediate

Program Prerequisites: Experience with Excel Error Functions is Recommended

Advance Preparation:None

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
  6:56Introduction to Excel Efficiency: Logic Functions
  6:58Comparing Cells Without Using IF
  7:08Two Levels of Nested IF Statements
  12:26Use F9 to Calculate Part of a Formula
  8:54IFS Function (Office 365 Only)
  10:07Introduction to IFERROR
  10:06IFERROR/VLOOKUP Alternative to IF
  8:12Introduction to IS Functions
  7:14Introduction to XOR
  8:51SUMIF with Range Criteria
  7:15SUMPRODUCT as SUMIFS Alternative
  5:05MIN/MAX and SMALL/LARGE
  7:14MINIFS Function (Office 365 Only)
CONTINUOUS PLAY
  1:45:29Excel Efficiency: Logic Functions
Supporting Materials
  PDFSlides: Excel Efficiency: Logic Functions
  PDFExcel Efficiency: Logic Functions Glossary/Index
  PDFWorkbook: Excel Efficiency: Logic Functions
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM