Excel Logic: IF, AND, OR, and Beyond

Author: David H. Ringstrom

In this comprehensive presentation, Excel expert David Ringstrom, CPA, briefly discusses the IF function and then, in detail, takes you beyond the basics. He 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 others. David’s assertion is that if you’re nesting more than a couple of IF functions, there’s probably a more refined and resilient approach you can take.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 and earlier) 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.

Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.

Publication Date: April 2022

Designed For
Practitioners who would benefit by learning more about Excel error functions.

Topics Covered

• Auditing portions of a formula by using the F9 key to temporarily convert part of a formula to a value
• Avoiding the complexity of nested IF statements with Excel's CHOOSE function
• Comparing the MIN, SMALL, MAX, and LARGE functions
• Comparing two cells directly to return TRUE or FALSE if they're exactly the same or not without using IF
• Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts
• Discovering the range of IS functions that can be used within IF statements to test for various conditions within a worksheet
• Employing the IFERROR function for situations where VLOOKUP returns #N/A
• Employing the ISERROR function to determine if a formula returns a # sign error as the basis for triggering alternate calculations
• Employing the SUBSTITUTE function as an alternative to complex formulas based on the IF function
• Improving the integrity of spreadsheets by using SUMIF to look up values in a more flexible fashion than VLOOKUP
• Incorporating comparison operators within SUMIF to sum numbers based on range criteria, such as greater than, less than, and so on
• Incorporating decisions into calculations with Excel's IF function

Learning Objectives

• Identify how to recall how to avoid complex IF statements
• Recognize how to apply the COUNTIF function to determine the number of times an item appears on a list
• Describe when to use the ISERROR function or the ISNA function vs. the IFERROR function
• Identify which function returns TRUE if two values being compared match down to the case level
• Describe how many levels of the IF function you can nest in Excel 2007 and later
• Recognize which ribbon tab in Excel 2007 and later the Evaluate Formula feature appears on
• Identify which argument the VLOOKUP function has
• Describe what the ISERROR function does
• Identify what SUMIF returns if it doesn't find a match
• Recognize the maximum number of criteria pairs you can use with the SUMIFS function

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Experience with Excel Error Functions Recommended.

Advance Preparation
None