Completed
) Some Functions still will NOT make Array Calculations, like SUMIFS, COUNTIFS and AVERGAEIFS
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Comprehensive Excel Dynamic Array Formula Lesson - The Power of Array Formulas
Automatically move to the next video in the Classroom when playback concludes
- 1 ) Introduction to the new Excel Calculation Engine and Array Formulas in Excel
- 2 ) OR Logical Test AVERAGE Array Formula. Delivers a single Answer. No Ctrl + Shift + Enter
- 3 ) How Old Single Cell Array Formula Behaved
- 4 ) How New Calc Engine Avoids Trouble with Array Formulas
- 5 ) FREQUENCY Function
- 6 ) How Old FREQUENCY Function Worked
- 7 ) FREQUENCY Function. New Calc Engine. Spill Automatically
- 8 ) Where Spilled Array Formula Lives. Refer to Spilled Array with F40#
- 9 ) Spill Error
- 10 ) Standard Deviation. Delivers a single Answer. No Ctrl + Shift + Enter
- 11 ) OR Logical Test Adding formula with SUMIFS & SUM Function, rather than SUMPRODUCT
- 12 ) SUMIFS and Function Argument Array Operation will Spill
- 13 ) Array Formula to create a Formula Report that is EAISER than using a PivotTable
- 14 ) Unique List using UNIQUE Function
- 15 ) Distinct List using UNIQUE
- 16 ) Unique List and FILTER to avoid zeroes
- 17 ) Unique Count Formula using COUNTA, UNIQUE and OR
- 18 ) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Single Column
- 19 ) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Multiple Columns
- 20 ) FILTER Function as Lookup with Multiple Lookup Values & Return Multiple Items (Boolean Logical Test)
- 21 ) #CALC! Error and the third argument in FILTER
- 22 ) SORT Function to sort a filtered list
- 23 ) Extract Sorted & Unique List from Mixed Data using SORT and UNIQUE
- 24 ) Extract Unique & Sorted List of Mixed Data in Single Cell using TEXTJOIN, SORT and UNIQUE
- 25 ) Spill Direct for Dynamic Arrays using Array Syntax
- 26 ) Variable Length Spilled Arrays using LARGE & SEQUENCE Function
- 27 ) SEQUENCY Function for incrementing Numbers in cells
- 28 ) SEQUENCY Function for incrementing Stepped Numbers in cells
- 29 ) MID, LEN and SEQUENCE to extract characters from a cell in a Row
- 30 ) MID, LEN, TRANSPOSE and SEQUENCE to extract characters from a cell in a Column
- 31 ) Dynamic PivotTable using SUMIFS and a number of new Array Functions
- 32 ) SINGLE Function and Implicit Intersection
- 33 ) Some Functions still will NOT make Array Calculations, like SUMIFS, COUNTIFS and AVERGAEIFS
- 34 ) RANDARRAY Function
- 35 ) Summary
