Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Download Excel START File: https://people.highline.edu/mgirvin/Y...
Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/ex...
This video is a comprehensive video about the new Office 365 Dynamic Array Formulas, Array Functions and Excel’s Calculation engine.
Topics:
1. (00:06) Introduction to the new Excel Calculation Engine and Array Formulas in Excel
2. (05:53) OR Logical Test AVERAGE Array Formula. Delivers a single Answer. No Ctrl + Shift + Enter
3. (07:22) How Old Single Cell Array Formula Behaved
4. (08:20) How New Calc Engine Avoids Trouble with Array Formulas
5. (09:08) FREQUENCY Function
6. (09:32) How Old FREQUENCY Function Worked
7. (10:00) FREQUENCY Function. New Calc Engine. Spill Automatically
8. (10:32) Where Spilled Array Formula Lives. Refer to Spilled Array with F40#
9. (12:45) Spill Error
10. (13:20) Standard Deviation. Delivers a single Answer. No Ctrl + Shift + Enter
11. (13:45) OR Logical Test Adding formula with SUMIFS & SUM Function, rather than SUMPRODUCT
12. (15:29) SUMIFS and Function Argument Array Operation will Spill
13. (17:00) Array Formula to create a Formula Report that is EAISER than using a PivotTable
14. (17:51) Unique List using UNIQUE Function
15. (18:18) Distinct List using UNIQUE
16. (20:18) Unique List and FILTER to avoid zeroes
17. (21:22) Unique Count Formula using COUNTA, UNIQUE and OR
18. (22:37) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Single Column
19. (24:00) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Multiple Columns
20. (24:22) FILTER Function as Lookup with Multiple Lookup Values & Return Multiple Items (Boolean Logical Test)
21. (24:44) #CALC! Error and the third argument in FILTER
22. (26:08) SORT Function to sort a filtered list
23. (27:50) Extract Sorted & Unique List from Mixed Data using SORT and UNIQUE
24. (28:38) Extract Unique & Sorted List of Mixed Data in Single Cell using TEXTJOIN, SORT and UNIQUE
25. (29:43) Spill Direct for Dynamic Arrays using Array Syntax
26. (30:50) Variable Length Spilled Arrays using LARGE & SEQUENCE Function
27. (32:00) SEQUENCY Function for incrementing Numbers in cells
28. (32:10) SEQUENCY Function for incrementing Stepped Numbers in cells
29. (32:28) MID, LEN and SEQUENCE to extract characters from a cell in a Row
30. (33:26) MID, LEN, TRANSPOSE and SEQUENCE to extract characters from a cell in a Column
31. (33:40) Dynamic PivotTable using SUMIFS and a number of new Array Functions
32. (35:15) SINGLE Function and Implicit Intersection
33. (37:45) Some Functions still will NOT make Array Calculations, like SUMIFS, COUNTIFS and AVERGAEIFS
34. (38:14) RANDARRAY Function
35. (39:01) Summary
Watch video Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) online without registration, duration hours minute second in high quality. This video was added by user excelisfun 19 October 2018, don't forget to share it with your friends and acquaintances, it has been viewed on our site 87,72 once and liked it 1.9 thousand people.