Rollingλ(): 5g Function for Rolling Calculations
Introduction
Excel's LAMBDA functions have opened up incredible possibilities. 5g functions are LAMBDA functions created in such a way as to be easily shared with others, easily imported into any Excel workbook, and easily used by anyone even if they know nothing about LAMBDA.
One such 5g function is Rollingλ(). Rollingλ() is an 'eta LAMBDA Helper' function designed to perform rolling calculations on dynamic arrays. This function introduces a flexible way to apply a moving window across a row of values and process them with a specified function, similar to how BYROW() processes blocks of cells row by row.
Syntax
Rollingλ( Values, WindowSize, WindowStart, [Function], [AllowPartial] )
Values (required): A row of cells or a row of values within an array.
WindowSize (required): The number of cells or values to include in the window.
WindowStart (required): The number of positions to shift the left edge of the window as it moves. Negative values shift left, 0 is the current cell, and positive values shift right.
Function (optional): An existing Excel function or a custom LAMBDA function to process the window. The default is SUM(), producing a rolling total.
AllowPartial (optional): Determines whether to allow partial windows at the row's edges. Defaults to FALSE, meaning partial windows return 0.
Key Use Cases
The versatility of Rollingλ() allows Excel modelers to automate many common rolling calculations, including:
1. Rolling Sum
=Rollingλ( J1#, 3, -2)
Calculates a rolling sum over the dynamic array starting in J1#, shifting a window of three cells that starts two cells to the left and proceeds to the end of J1#. Partial window results are 0.
2. Rolling Average for Smoothing Data
=Rollingλ( J1#, 3, -2, AVERAGE, TRUE)
Applies a rolling average to the dataset, useful for smoothing fluctuations in financial forecasts. Partial windows are averaged.
3. Straight-Line Depreciation Calculation
=Rollingλ( J1#, 2, -1) / 2
Computes depreciation over capital expenditures with a two-year lifespan by summing each 2 years (current cell and 1 to the left) in J1# and then dividing each sum by 2 (life span).
4. Rollingλ with a custom LAMBDA function
=Rollingλ( J1#, 3, -2, LAMBDA( Window, TEXTJOIN( ",", , Window)))
Concatenates the window's contents with commas, useful for formatting reports or preparing structured data outputs.
Why Rollingλ() Matters
For Excel financial modelers, Rollingλ() simplifies working with dynamic arrays. This is particularly useful for time series analysis, moving averages, and financial forecasting.
For Excel LAMBDA creators, Rollingλ() demonstrates how LAMBDA functions can be written to behave like eta LAMBDA helper functions
Conclusion
Rollingλ() is a powerful tool for enhancing Excel's calculation capabilities, making it easier to apply rolling analyses across datasets. Whether you're a financial modeler looking to streamline calculations or a LAMBDA developer exploring new function architectures, this function offers valuable insights and practical utility.
If you found Rollingλ() useful, share your thoughts and let’s discuss more innovative ways to leverage LAMBDA in Excel!
Formula
Below is a picture of the code formatted for easy reading. Below this picture is the text version that can by copy/pasted into the Advanced Formula Environment.
Finance & Technology Integration | Wealth Management | Unlock Tech-Driven Growth
5moImpressive.
Executive Director at BTG Pactual
5moAwesome! I'm curious if there's a less computationally intensive alternative to the TAKE/TAKE method for applying a rolling function. Specifically, is there a faster algorithm for calculating a rolling max in Excel?
Technical Fellow at NAFEMS
5moWe have all had a go at this, though in my case with a little less polish and attention to detail 😞 My function did not perform any aggregation; it just went as far as returning a reference to a range for each value (assuming the values were presented as a range and not simply an array). ROLLINGRANGEλ = LAMBDA(values, n, LET( rows, SEQUENCE(ROWS(values)), MAP(rows, LAMBDA(k, LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), LAMBDA(rng) ) ) ) ) ); The unfinished nature of the work was evident from the fact that I needed a further Lambda function to apply an eta reduce function to the thunked ranges MAPϑ = LAMBDA(ϑ, FNλ, MAP(ϑ, LAMBDA(ϑ, FNλ(ϑ())))); Not quite up to the standard of Craig's 5g programming!!! Could do better.
Data-driven by nature
5moSnap! https://www.flexyourdata.com/blog/excel-lambda-pd-rolling-aggregates-calculate-rolling-sum-in-excel-and-much-more/
Excel Robot
5moThis is great, Craig. The only thing better than a handy Lambda is a well documented and tested 5G version of that lambda. Thank you, I look forward to playing with this!