COURSE INFORMATION
The aim of the course is to enable you to understand and apply Functions and use nested Functions where more than one function will be required. A variety of other functions from the content list below will be covered from categories delegates require. As new functions are added by Microsoft these will be included where appropriate.
- Functions types and the Insert Function tool
- Using Nested functions to create more complex formulas
- Formula Auditing Tools
Nesting Functions
- Using Complex Functions
- AND, OR, and NOT functions with IF
- Using IFS for multiple conditions
- SUMIF, COUNTIF, COUNTBLANK
Formula Auditing Tools
- Trace Dependant and Precedent cells
- Evaluate formula tool
Date and Time Category
- TODAY, NOW, NETWORKDAYS, EOMONTH
Text Category
- Extracting specific data with the MID, LEFT and RIGHT functions
- Using the TRIM function to remove unwanted spaces in a cell
- Using ampersands and CONCATENATE to combine data from different cells
- Using TEXTSPLIT, TEXTBEFORE. TEXTAFTER to extract data
Information Category
- Extracting information with the CELL and INFO functions
- ISERR, ISERROR, IFERROR, and ISNA error-checking functions
Advanced Lookup and Reference Category
- Using MATCH and INDEX functions together
- Using the XLOOKUP function
- Using VSTACK, HSTACK, TOCOL, TOROW, TAKE, DROP to append data into an array
- UNIQUE and EXPAND functions
Statistical Category
- Finding the middle value with MEDIAN and most common value with MODE
- ROUND, ROUNDUP, and ROUNDDOWN functions
Reference Category and Advanced Data Validation
- Getting data from remote cells with the OFFSET function
- Using INDIRECT with Data Validation for two-tiered pick lists
A good knowledge of the following features is beneficial:
- Formula structure and rules
- Absolute cell references
- Use the Insert Function tool
- Aggregate Functions (SUM, COUNT, AVERAGE, MAX, MIN)
- Range Names
- £145
- Half day trainer led or virtual