Deprecated: Creation of dynamic property CF\WordPress\DataStore::$logger is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php on line 23
Deprecated: Creation of dynamic property CF\WordPress\Proxy::$pluginAPI is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/Proxy.php on line 31
Deprecated: file_get_contents(): Passing null to parameter #2 ($use_include_path) of type bool is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/codelights-shortcodes-and-widgets/codelights.php on line 20
Warning: session_start(): Session cannot be started after headers have already been sent in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/wordpress/src/class/wordpress.php on line 53
Deprecated: Return type of Requests_Cookie_Jar::offsetExists($key) should either be compatible with ArrayAccess::offsetExists(mixed $offset): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/Requests/Cookie/Jar.php on line 63
Deprecated: Return type of Requests_Cookie_Jar::offsetGet($key) should either be compatible with ArrayAccess::offsetGet(mixed $offset): mixed, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/Requests/Cookie/Jar.php on line 73
Deprecated: Return type of Requests_Cookie_Jar::offsetSet($key, $value) should either be compatible with ArrayAccess::offsetSet(mixed $offset, mixed $value): void, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/Requests/Cookie/Jar.php on line 89
Deprecated: Return type of Requests_Cookie_Jar::offsetUnset($key) should either be compatible with ArrayAccess::offsetUnset(mixed $offset): void, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/Requests/Cookie/Jar.php on line 102
Deprecated: Return type of Requests_Cookie_Jar::getIterator() should either be compatible with IteratorAggregate::getIterator(): Traversable, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/Requests/Cookie/Jar.php on line 111
Deprecated: Return type of Requests_Utility_CaseInsensitiveDictionary::offsetExists($key) should either be compatible with ArrayAccess::offsetExists(mixed $offset): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/Requests/Utility/CaseInsensitiveDictionary.php on line 40
Deprecated: Return type of Requests_Utility_CaseInsensitiveDictionary::offsetGet($key) should either be compatible with ArrayAccess::offsetGet(mixed $offset): mixed, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/Requests/Utility/CaseInsensitiveDictionary.php on line 51
Deprecated: Return type of Requests_Utility_CaseInsensitiveDictionary::offsetSet($key, $value) should either be compatible with ArrayAccess::offsetSet(mixed $offset, mixed $value): void, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/Requests/Utility/CaseInsensitiveDictionary.php on line 68
Deprecated: Return type of Requests_Utility_CaseInsensitiveDictionary::offsetUnset($key) should either be compatible with ArrayAccess::offsetUnset(mixed $offset): void, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/Requests/Utility/CaseInsensitiveDictionary.php on line 82
Deprecated: Return type of Requests_Utility_CaseInsensitiveDictionary::getIterator() should either be compatible with IteratorAggregate::getIterator(): Traversable, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/Requests/Utility/CaseInsensitiveDictionary.php on line 91
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Deprecated: Creation of dynamic property ternplugin\youtube_video::$post is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/plugin/src/class/youtube_video.php on line 64
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
{"id":777,"date":"2018-10-19T17:28:09","date_gmt":"2018-10-20T00:28:09","guid":{"rendered":"https:\/\/bonbonsguide.com\/wp\/?p=777"},"modified":"2018-12-02T10:51:29","modified_gmt":"2018-12-02T17:51:29","slug":"comprehensive-excel-dynamic-array-formula-lesson-the-power-of-array-formulas-emt-1516","status":"publish","type":"post","link":"https:\/\/bonbonsguide.com\/wp\/blog\/2018\/10\/comprehensive-excel-dynamic-array-formula-lesson-the-power-of-array-formulas-emt-1516\/","title":{"rendered":"Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)"},"content":{"rendered":"Download Excel START File: https:\/\/ift.tt\/2ysCu6P
\nEntire page with all Excel Files for All Videos: https:\/\/ift.tt\/1kSFWvs
\nThis video is a comprehensive video about the new Office 365 Dynamic Array Formulas, Array Functions and Excel\u2019s Calculation engine.
\nTopics:
\n1. (00:06) Introduction to the new Excel Calculation Engine and Array Formulas in Excel
\n2. (05:53) OR Logical Test AVERAGE Array Formula. Delivers a single Answer. No Ctrl + Shift + Enter
\n3. (07:22) How Old Single Cell Array Formula Behaved
\n4. (08:20) How New Calc Engine Avoids Trouble with Array Formulas
\n5. (09:08) FREQUENCY Function
\n6. (09:32) How Old FREQUENCY Function Worked
\n7. (10:00) FREQUENCY Function. New Calc Engine. Spill Automatically
\n8. (10:32) Where Spilled Array Formula Lives. Refer to Spilled Array with F40#
\n9. (12:45) Spill Error
\n10. (13:20) Standard Deviation. Delivers a single Answer. No Ctrl + Shift + Enter
\n11. (13:45) OR Logical Test Adding formula with SUMIFS & SUM Function, rather than SUMPRODUCT
\n12. (15:29) SUMIFS and Function Argument Array Operation will Spill
\n13. (17:00) Array Formula to create a Formula Report that is EAISER than using a PivotTable
\n14. (17:51) Unique List using UNIQUE Function
\n15. (18:18) Distinct List using UNIQUE
\n16. (20:18) Unique List and FILTER to avoid zeroes
\n17. (21:22) Unique Count Formula using COUNTA, UNIQUE and OR
\n18. (22:37) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Single Column
\n19. (24:00) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Multiple Columns
\n20. (24:22) FILTER Function as Lookup with Multiple Lookup Values & Return Multiple Items (Boolean Logical Test)
\n21. (24:44) #CALC! Error and the third argument in FILTER
\n22. (26:08) SORT Function to sort a filtered list
\n23. (27:50) Extract Sorted & Unique List from Mixed Data using SORT and UNIQUE
\n24. (28:38) Extract Unique & Sorted List of Mixed Data in Single Cell using TEXTJOIN, SORT and UNIQUE
\n25. (29:43) Spill Direct for Dynamic Arrays using Array Syntax
\n26. (30:50) Variable Length Spilled Arrays using LARGE & SEQUENCE Function
\n27. (32:00) SEQUENCY Function for incrementing Numbers in cells
\n28. (32:10) SEQUENCY Function for incrementing Stepped Numbers in cells
\n29. (32:28) MID, LEN and SEQUENCE to extract characters from a cell in a Row
\n30. (33:26) MID, LEN, TRANSPOSE and SEQUENCE to extract characters from a cell in a Column
\n31. (33:40) Dynamic PivotTable using SUMIFS and a number of new Array Functions
\n32. (35:15) SINGLE Function and Implicit Intersection
\n33. (37:45) Some Functions still will NOT make Array Calculations, like SUMIFS, COUNTIFS and AVERGAEIFS
\n34. (38:14) RANDARRAY Function
\n35. (39:01)
\n36. Summary<\/p>\n