https://bugs.documentfoundation.org/show_bug.cgi?id=164507
--- Comment #2 from Óvári <[email protected]> --- https://support.microsoft.com/en-gb/office/pivotby-function-de86516a-90ad-4ced-8522-3a25fac389cf A lambda function or eta reduced lambda (SUM, AVERAGE, COUNT, etc) that defines how to aggregate the values. ----------------------------------- https://support.microsoft.com/en-us/office/groupby-function-5e08ae8c-6800-4b72-b623-c41773611505 An explicit or eta reduced lambda (SUM, PERCENTOF, AVERAGE, COUNT, etc) that is used to aggregate values. A vector of lambdas can be provided. If so, the output will have multiple aggregations. The orientation of the vector will determine whether they are laid out row- or column-wise. ---- https://spreadsheetweb.com/exploring-eta-lambda-in-excel-a-comprehensive-and-detailed-guide-with-examples/ A. What is Eta Lambda? ====================== What is Eta Lambda? Eta Lambda refers to the application of eta reduction, a concept from lambda calculus, to Excel functions. In simple terms, eta reduction eliminates unnecessary layers of abstraction, allowing for a more concise representation of functions. B. Technical Background: Eta Reduction in Lambda Calculus ========================================================= In lambda calculus, functions like: LAMBDA(x, SUM(x)) can be simplified to: SUM This works because the SUM function and the LAMBDA function wrapping it behave identically for all inputs. Excel incorporates this principle into dynamic functions, allowing you to skip the LAMBDA wrapper if you are only applying a single function. For example: =BYROW(array, LAMBDA(x, SUM(x))) can be simplified to: =BYROW(array, SUM) C. Benefits of Eta Lambda ========================= 1. Concise Syntax: Reduces verbosity, making formulas shorter and easier to write. 2. Improved Readability: Simplified formulas are easier for collaborators to understand. 3. Dynamic Adaptability: Automatically adjusts to changes in data ranges or structures. 4. Error Reduction: Minimizes the risk of mistakes caused by overly complex formulas. 5. Streamlined Maintenance: Easier to debug and update formulas. D. Functions Compatible with Eta Lambda ======================================= Eta Lambda works with Excel's dynamic array functions, which are designed to process arrays, rows, and columns dynamically. These include: * BYROW: Applies a function to each row in a range. * BYCOL: Applies a function to each column in a range. * SCAN: Generates running totals or cumulative values across an array. * REDUCE: Aggregates values in an array using a custom operation. E. Detailed Examples of Eta Lambda in Action ============================================ 1. Using BYROW with SUM ----------------------- Objective: Calculate the total score for each student. Formula with LAMBDA: =BYROW(B3:F6, LAMBDA(x, SUM(x))) Simplified Formula with Eta Lambda: =BYROW(B3:F6, SUM) 2. Using BYROW with AVERAGE --------------------------- Objective: Calculate the average score for each student. Formula with LAMBDA: =BYROW(B3:F6, LAMBDA(x, AVERAGE(x))) Simplified Formula with Eta Lambda: =BYROW(B3:F6, AVERAGE) 3. Using BYROW with MAX ----------------------- Objective: Find the highest score for each student. Formula with LAMBDA: =BYROW(B3:F6, LAMBDA(x, MAX(x))) Simplified Formula with Eta Lambda: =BYROW(B3:F6, MAX) 4. Using BYCOL with SUM ----------------------- Objective: Calculate the total score for each quiz. Formula with LAMBDA: =BYCOL(B3:F6, LAMBDA(x, SUM(x))) Simplified Formula with Eta Lambda: =BYCOL(B3:F6, SUM) 5. Using SCAN for Running Totals -------------------------------- Objective: Compute a running total for Alice’s scores. Formula: =SCAN(0, B3:F3, LAMBDA(a, b, a + b)) 6. Using REDUCE for Aggregation ------------------------------- Objective: Find the cumulative sum of all scores in the dataset. Formula with LAMBDA: =REDUCE(0, B3:F6, LAMBDA(a, b, a + b)) Simplified Formula with Eta Lambda: =REDUCE(0, B3:F6, SUM) F. Practical Applications of Eta Lambda ======================================= 1. Employee Performance Analysis: * Use BYROW to calculate total scores for individual employees. * Use BYCOL to evaluate department-level performance metrics. 2. Financial Dashboards: * Use SCAN for monthly cumulative revenue. * Use REDUCE to calculate annual totals dynamically. 3. Inventory Management: * Use BYROW to calculate inventory usage per product. * Use BYCOL to determine total stock for each category. 4. Dynamic Reporting: * Build auto-updating reports with SCAN for progressive summaries and REDUCE for final aggregates. G. Best Practices for Using Eta Lambda ====================================== 1. Start Simple: Begin with straightforward operations like SUM or AVERAGE to familiarize yourself with the syntax. 2. Plan Your Data Structure: Dynamic functions work best with clean, well-structured datasets. 3. Test Before Simplifying: Ensure formulas behave as expected before applying eta reduction. 4. Document Your Formulas: Add comments to clarify the purpose of complex calculations. H. Conclusion ============= Eta Lambda is a powerful addition to Excel’s functionality, enabling users to simplify dynamic calculations without sacrificing accuracy or flexibility. By leveraging functions like BYROW, BYCOL, SCAN, and REDUCE, you can streamline your workflows, reduce errors, and improve the clarity of your formulas. If you’re looking to transform your Excel spreadsheets with the latest innovations, Eta Lambda is an essential tool to explore. Try it out in your next project and experience the benefits firsthand! -- You are receiving this mail because: You are the assignee for the bug.
