Highline Excel 2016 Class 05

Excel Array Formulas: Comprehensive Lessons: 12 Examples

DESCRIPTION

In this video learn about:
(00:04) Download Files
(00:24) Aggregate vs. Array Calculations & Define Array
(02:55) Define Array Formulas
(05:49) Example of non-array formula. Standard Formula: Formulas that calculate a single answer. Goal: Calculate Total Expense and Total Net Income.
(06:43) 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.
(10:00) How to decide whether or not you must use Ctrl + Shift + Enter to put the array formula into the cell.
(12:06) Implicit Intersection.
(14:49) SUMPRODUCT function to house an array calculation to avoid using Ctrl + Shift + Enter.
(16:46) Array Formula 2: Math Array Operations. Goal: Calculate Overall Total Expense in a single cell.
(19:34) Array Formula 3: Math Array Operations. Goal: Calculate Overall Net Income in a single cell.
(20:48) 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.
(25:06) 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.
(28:32) 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.
(32:16) Array Formula 8: Function Argument Array Operation in criteria1 argument. Goal: Score Myers-Briggs Test in Human Resource Department.
(35:21) Array Formula 9: Function Argument Array Operation in criteria1 argument. Join Array Operation. Goal: Score Myers-Briggs Test in Human Resource Department.
(37:15) 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.
(38:12) List of Functions that can NEVER do Array Calculations.
(43:11) 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.
(45:42) Array Formula 12: Built-in Array Function: FREQUENCY. Goal: Count how many sales are in each category.
(50:31) Summary

WORKBOOKS

Download “Highline Excel 2016 Class 05” Highline-Excel-2016-Class-05.zip – Downloaded 39 times – 708 KB







Be the first to comment

Leave a Reply