Excel Advanced Formulas and Functions Training Course

Best Practices for Nested Functions

When working with nested functions in MS Excel, it’s important to follow best practices to ensure your formulas are efficient, readable, and maintainable. Here are some tips for using nested functions effectively:

Limit the Nesting Levels: Try to keep the nesting to as few levels as possible.

Use Alternative Functions: Consider using functions like IFS, XLOOKUP, CHOOSE, or SWITCH.

Start Simple: Begin with the most basic part of your formula and build outwards.

Parentheses Matching: Make sure every opening parenthesis has a corresponding closing parenthesis.

Test Your Formulas: Always test your formulas with different inputs.

Readability: Structure your formulas in a way that they are easy to read and understand.

Avoid Repetition: Use helper columns if repeating calculations.

Document Your Work: Document your logic for future reference.

In MS Excel, nested functions are executed from the inside out.

Statistical and Forecasting Tools

LARGE(array, k): Returns the k-th largest value.

SMALL(array, k): Returns the k-th smallest value.

ROUND(number, num_digits): Rounds a number.

CORREL(array1, array2): Calculates the correlation coefficient.

SLOPE(known_y’s, known_x’s): Returns slope of regression line.

FORECAST.LINEAR: Predicts a future value using linear regression.

FORECAST.ETS: Predicts values using exponential smoothing.

Date, Time & Text Functions

WORKDAY: Returns a date before/after workdays.

DATEDIF: Calculates date differences.

EDATE: Returns a date a set number of months from start.

WEEKNUM: Returns week number.

UPPER, PROPER: Adjust text capitalisation.

FIND & SEARCH: Find substring positions.

MID, LEFT, RIGHT: Extract text.

LEN: Count characters.

TRIM: Remove extra spaces.

TEXTJOIN, CONCAT: Join text items.

Array Formulas Overview

TREND: Calculates linear trend line.

GROWTH: Predicts exponential growth.

FREQUENCY: Calculates frequency distribution.

Array formulas allow multiple calculations on arrays and support dynamic spill behaviour.

Dynamic Array Tools

UNIQUE: Returns unique values.

SPILL: Automatic array output behaviour.

SORT and SORTBY: Sort data dynamically.

FILTER: Filters data based on criteria.

Dynamic arrays reduce need for older array formulas.

Advanced Lookup & Reference

XLOOKUP: Searches range/array and returns matching value.

XMATCH: Returns position of matching value.

ADDRESS: Returns cell reference as text.

INDIRECT: Returns reference specified by text.

OFFSET: Returns reference offset from given cell.

CHOOSE: Returns value from list by index.

Auditing Excel Formulas

Trace Precedents: Shows cells supplying data.

Trace Dependents: Shows cells depending on selected cell.

Show Formulas: Displays formulas instead of results.

Error Checking: Helps find and correct formula errors.

Evaluate Formula: Step through calculations.

Remove Arrows: Clears precedent/dependent arrows.

IS Information Functions

ISERR: Checks for errors except #N/A.

ISERROR: Checks for all error types.

IFERROR: Allows alternative output when error found.

Example: =IF(ISERROR(A1), 'Error in calculation', A1*2).