Overview

Dive into a comprehensive 52-minute video tutorial on Excel 2016 array formulas, featuring 12 practical examples. Learn to differentiate between aggregate and array calculations, understand array formula basics, and master advanced techniques. Explore math array operations, function argument array operations, and built-in array functions like TRANSPOSE and FREQUENCY. Discover how to use SUMPRODUCT for array calculations, apply IF functions to filter values, and create compact solutions for complex calculations. Gain insights into when to use Ctrl + Shift + Enter, understand implicit intersection, and learn about functions that cannot perform array calculations. By the end of this tutorial, you'll be equipped to efficiently handle various Excel tasks, from calculating total expenses and net income to scoring Myers-Briggs tests and analyzing sales data using array formulas.

Syllabus

) 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.