In this three-hour session for Windows and Mac users, you will learn how to write complex formulas using Excel's advanced built-in functions. You will expand your library of functions to include lookup and reference functions, conditional logic, database summarization functions, and dynamic array functions. You will also become expert at using mixed references to make copying formulas horizontally and vertically work the way you want it to.
Prerequisites
This class is ideally suited for those who have attended the Excel Beginning 2: Simple Formulas class or who are already comfortable using simple functions like SUM, AVERAGE, COUNT, LEFT, MID, RIGHT, and PMT.
Outline
Lookup and Reference Functions
- VLOOKUP and HLOOKUP to retrieve values from a list
- nesting functions inside functions
- absolute references and mixed references
- conditional formatting
- lookups in tables, and structured references
- range lookups
- range names
- INDEX and MATCH for more versatile lookups
- creating in-cell drop-down menus
- XLOOKUP function
Logical Functions
- IF function to construct conditional logic
- AND and OR functions for multiple conditions
- IFS function
- IFERROR function
- COUNTIF, COUNTIFS, SUMIF, SUMIFS
- database functions DCOUNT, DSUM
Dynamic Array Formulas
- SEQUENCE function
- SORT and FILTER functions
- UNIQUE function
