Excel expert David Ringstrom, CPA, begins this comprehensive on-demand webcast with a brief discussion of the IF function and then goes beyond the basics. He explains what can go awry with the IF function and ways to improve the integrity of your decision-making formulas. David shares alternatives to 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’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 Excel 2016. He’ll draw to your attention 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.
- Learning about the MAXIFS function available in certain versions of Excel 2016.
- Understanding when you might wish to use ISERROR or ISNA instead of IFERROR.
- Auditing portions of a formula by using the F9 key to temporarily convert part of a formula to a value.
- Streamlining the decision-making process with the IFS function in Office 365.
- Testing for two or more conditions at once with Excel’s AND function.
- Employing the SUBSTITUTE function as an alternative to complex formulas based on the IF function.
- Comparing the MIN, SMALL, MAX, and LARGE functions.
- Using the COUNTIF function to determine the number of times an item appears in a list.
- Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
- Employing the IFERROR function for situations where VLOOKUP returns #N/A.
- Reconstructing spreadsheet data to use VLOOKUP as a better alternative to nesting IF functions.
- Mastering the IFERROR function to display alternate values in lieu of a # sign error.
Delivery Method: Online QAS Self Study.
Version: David teaches from Excel 2010 in this presentation but every aspect of the material applies equally to Excel 2007, 2013, and 2016.
Prerequisites: A familiarity with Excel.
Advanced Preparation: None