In this three-hour session for Windows and Mac users, you will learn to write complex formulas using Excel's built-in functions and array formulas. You will expand your library of functions to include financial, text parsing, logical, lookup and database functions. You will also learn to write array formulas, which are capable of performing multiple calculations in a single result. Finally, you will gain essential formula auditing and troubleshooting skills.
Prerequisites
This class is ideally suited for those who have attended the Excel Beginning 2: Simple Formulas class or who are already comfortable creating basic formulas like SUM, AVERAGE, COUNT, LEFT, MID, RIGHT and PMT.
Class Outline
Advanced Formulas using Functions
- IF to construct conditional logic
- nesting functions inside functions
- VALUE to convert strings to values
- SEARCH to aid in parsing text strings
- VLOOKUP and XLOOKUP to retrieve values from a table
- INDEX and MATCH for more dynamic lookups
- creating in-cell drop-down menus
- DSUM to sum table data filtered by criteria
Array Formulas
- understand array formulas
- write array formulas that perform multiple calculations to generate a single result
- write array formulas that calculate multiple results
Troubleshooting Advanced Formulas
- editing formulas
- auditing your formulas
- tracing precedents and dependents
- inserting comments
- using the Watch window