Excel Array Formulas - Comprehensive Lessons with 12 Examples

) Download Files.
) Aggregate vs. Array Calculations & Define Array.
) Define Array Formulas.
) Example of non-array formula. Standard Formula: Formulas that calculate a single answer. Goal: Calculate Total Expense and Total Net Income..
) Array Formula 1: Math Array Operation. Goal: Calculate Total Expense for each month without making calculations in many cells (we don’t need all the individual numbers). Main reason we use Array Formulas: To create compact solution to do all the intermediate steps in a single cell..
) How to decide whether or not you must use Ctrl + Shift + Enter to put the array formula into the cell..
) Implicit Intersection..
) SUMPRODUCT function to house an array calculation to avoid using Ctrl + Shift + Enter..
) Array Formula 2: Math Array Operations. Goal: Calculate Overall Total Expense in a single cell..
) Array Formula 3: Math Array Operations. Goal: Calculate Overall Net Income in a single cell..
) Formula 4: Use more than one argument in SUMPRODUCT. Goal: Calculate total Bank Deposit. Learn about SUMPRODUCT and discussion about size and dimensions of arrays in array calculations..
) Array Formula 5: Function Argument Array Operation in k argument. Goal: Add 3 biggest and 3 smallest. Use Array Constant to avoid Ctrl + Shift + Enter..
) Array Formula 6: Function Argument Array Operation in criteria1 argument. Goal: Add Sales for each Sales Team. Example of using an Array Formula to create a MUCH smaller formula than non-Array Formula alternatives..
) Array Formula 8: Function Argument Array Operation in criteria1 argument. Goal: Score Myers-Briggs Test in Human Resource Department..
) Array Formula 9: Function Argument Array Operation in criteria1 argument. Join Array Operation. Goal: Score Myers-Briggs Test in Human Resource Department..
) Array Formula 10: IF function array formula to filter out values for an aggregate function. Goal: Calculate Average Gross Profit for Each Sales Rep. Learn about how to use IF Function to filter out values that we don’t want for our aggregate calculation..
) List of Functions that can NEVER do Array Calculations..
) Array Formula 11: Built-in Array Function: TRANSPOSE. Goal: Transpose rows and columns so we have Assumption Table that allows Mixed Cell References. Example of Array Formula where we enter multiple values as an array into multiple cells simultaneously..
) Array Formula 12: Built-in Array Function: FREQUENCY. Goal: Count how many sales are in each category..
) Summary.