Completed
) Download Files
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Excel Array Formulas - Comprehensive Lessons with 12 Examples
Automatically move to the next video in the Classroom when playback concludes
- 1 ) Download Files
- 2 ) Aggregate vs. Array Calculations & Define Array
- 3 ) Define Array Formulas
- 4 ) Example of non-array formula. Standard Formula: Formulas that calculate a single answer. Goal: Calculate Total Expense and Total Net Income.
- 5 ) 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 Fo…
- 6 ) How to decide whether or not you must use Ctrl + Shift + Enter to put the array formula into the cell.
- 7 ) Implicit Intersection.
- 8 ) SUMPRODUCT function to house an array calculation to avoid using Ctrl + Shift + Enter.
- 9 ) Array Formula 2: Math Array Operations. Goal: Calculate Overall Total Expense in a single cell.
- 10 ) Array Formula 3: Math Array Operations. Goal: Calculate Overall Net Income in a single cell.
- 11 ) 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.
- 12 ) 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.
- 13 ) 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 Form…
- 14 ) Array Formula 8: Function Argument Array Operation in criteria1 argument. Goal: Score Myers-Briggs Test in Human Resource Department.
- 15 ) Array Formula 9: Function Argument Array Operation in criteria1 argument. Join Array Operation. Goal: Score Myers-Briggs Test in Human Resource Department.
- 16 ) 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…
- 17 ) List of Functions that can NEVER do Array Calculations.
- 18 ) 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 multipl…
- 19 ) Array Formula 12: Built-in Array Function: FREQUENCY. Goal: Count how many sales are in each category.
- 20 ) Summary
