Excel Advanced 2: Advanced Formulas

In this half-day session, 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.

Outline:

Advanced Formulas using Functions

• PMT and PV financial calculations
• LEFT, MID and RIGHT to extract text
• nesting functions inside functions
• VALUE to convert strings to values
• IF to construct conditional logic
• SEARCH to aid in parsing text strings
• VLOOKUP 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
• TRIM to clean up text

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

Prerequisites: This class is ideally suited for those who have attended the Simple Functions class or who are already comfortable creating basic formulas like SUM, AVERAGE and COUNT.