Download Excel File: https://excelisfun.net/files/EMT1763.xlsx
Learn how to use the Microsoft 365 BYROWS & LAMBDA functions to spill aggregate results, such as SUM and MEDIAN.
Topics:
(00:00) Introduction
(00:20) Formulas that can spill, including multiplication operation and functions like SUMIFS
(01:10) Aggregate Formulas that can NOT spill, like SUM and MEDIAN
(02:01) LAMBDA is a function that delivers a function value
(02:29) LAMBDA with Helper Functions like BYROWS to spill the aggregate result, like SUM or MEDIAN
(04:27) Spilling reports with BYROWS & LAMBDA
(04:42) Advantages to Dynamic Spilled Array Formulas
(05:21) SORT and UNIQUE Array Functions to build first column of spilled report: sorted, unique list of product names.
(05:37) BYROWS, LAMBDA, MEDIAN and IF functions to build first column of spilled report: median values for each product.
(06:40) Explanation of cr variable in IF function to create the correct conditions for each median calculation with two references: one in LAMBDA and one in BYROWS.
(07:38) Single cell Product Median Report using the functions: LET, BYROWS, LAMBDA, SORT, UNIQUE, MEDIAN and IF.
(09:09) Explanation of cr variable in IF function to create the correct conditions for each median calculation with three references: one in LAMBDA and one in BYROWS and one in LET.
(09:33) Test LET function.
(09:54) CHOOSE function with Array Syntax to join two separate columns into one report.
(10:43) Use Alt Enter to make large Array Formula Easier to Read! Hot tip from Microsoft Excel Recal Or Die YouTube Teammate.
(11:38) Summary of video
(12:18) End links