Completed
) Introduction to the new Excel Calculation Engine and Array Formulas in Excel
Class Central Classrooms beta
YouTube playlists 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
