Excel Agility: Mastering Advanced Formulas
Excel expert David H. Ringstrom, CPA, shows you various ways to make sense of complicated formulas within Microsoft Excel in this insightful presentation. Sometimes your formulas can grow out of control, or, more likely, you’ve inherited spreadsheets from others that you’re to take ownership of. You’ll have many tricks at your disposal to quickly decipher even the most complex Excel formulas after attending this webcast.
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.
Topics Typically Covered:
• Comparing the MIN, SMALL, MAX, and LARGE functions.
• Using the MATCH function to find the position of an item on a list.
• Learning about the IFNA function available in Excel 2013 and later.
• Avoiding the complexity of nested IF statements with Excel’s CHOOSE function.
• Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
• Learning why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP.
• Using the SUMIFS function to sum values based on multiple criteria.
• Using the SUMIF function to summarize data based on a single criterion.
• Seeing how to view two worksheets from within the same workbook at the same time.
• Discovering how to use wildcards and multiple criteria within lookup formulas.
• Seeing what types of user actions can trigger #REF! errors.
• Learning how VLOOKUP stops looking after it finds an initial match within a list.
• List the benefits of using range names.
• Apply the Trace Precedents and Trace Error features to identify linked cells.
• Apply Excel’s IFERROR function to compensate for temporary situations where a formula may return an error.