Excel Update: Logic Functions: IF, AND, OR, and More

If you're looking for ways to improve the integrity of your decision-making formulas other than relying on Excel's IF function, you'll find this presentation quite helpful.

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 he then shares alternatives to using the IF function, including IFNA, MINIFS, SUMPRODUCT, and others.

David's assertion is that if you're nesting more than a couple of IF functions, there are probably more refined and resilient approaches 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 version of Excel (formerly known as Office 365). David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, 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 2019, Excel 2016, and so on.

Areas Covered in the Session:

• Improving the integrity of spreadsheets by using SUMIF to look up values in a more flexible fashion than VLOOKUP
• Using the COUNTIF function to determine the number of times an item appears in a list
• Avoiding the complexity of nested IF statements with Excel's CHOOSE function
• Using the SUMIFS function to sum values based on multiple criteria
• Discovering the range of IS functions that can be used within IF statements to test for various conditions within a worksheet
• Stepping through formulas in slow motion with the Evaluate Formulas feature
• Reconstructing spreadsheet data to use VLOOKUP as a better alternative to nesting IF functions
• Understanding the risks and complications of nesting too many levels of IF functions
• Learning about the IFNA function available in Excel 2013 and later
• Incorporating decisions into calculations with Excel's IF function
• Employing the IFERROR function for situations where VLOOKUP returns #N/A
• Understanding when you might wish to use ISERROR or ISNA instead of IFERROR

• Recall how to avoid complex IF statements
• Apply the AND function to test for two or more conditions at once
• Define when to use the ISERROR function or the ISNA function vs the IFERROR function

• Practitioners who wish to improve the integrity of their decision-making formulas using a variety of Excel’s logic functions