Comprehensive Excel Dynamic Array Formula Lesson - The Power of Array Formulas

Comprehensive Excel Dynamic Array Formula Lesson - The Power of Array Formulas

ExcelIsFun via YouTube Direct link

) SEQUENCY Function for incrementing Numbers in cells

27 of 35

27 of 35

) SEQUENCY Function for incrementing Numbers in cells

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

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Someone learning on their laptop while sitting on the floor.