Create a 5g Function: RunTotRowsλ()

NOTE! This article was written with assistance from Google's Gemini AI.

Introduction

In the world of Excel, complex formulas can often be a barrier to productivity. That's where 5g functions come in. Created using Excel's LAMBDA function, these user-defined functions transform intricate formulas into simple, easy-to-use tools that anyone of any skill level can use in any workbook. One such function, RunTotRowsλ() (Running Totals by Rows), simplifies the task of creating running totals for rows within an array.

Understanding RunTotRowsλ()

RunTotRowsλ() is designed to streamline the process of calculating cumulative totals for each row in an array. Whether we are working with a single-row array or a multi-row dataset, this function provides a straightforward solution, no LAMBDA skills required.

A Real-World Example

To illustrate the power of RunTotRowsλ(), let's consider a scenario where we have an array representing product orders (Demand) and product production for a month. Each row represents a different product, and each column represents a day. We can subtract the Demand array from the Production array to determine inventory requirements. Applying RunTotRowsλ() to that array projects how much on-hand inventory we will have, or how much we will need (negative numbers) to satisfy demand (see below).

The workbook from which the above figure comes from can be downloaded using this link:

https://www.dropbox.com/scl/fi/6blu6jo8cdxksfc0hkrp4/WIP.xlsx?rlkey=36pva6oce61t9qr3t2e7u881b&dl=1

CAUTION! This workbook (WIP.xlsx) is a work in process and may change.

Key Features of any 5g Function()

5g functions are LAMBDA functions specifically designed to be used by anyone, of any skill level, in any workbook. To meet that objective, 5g functions mimic, as best we can, Excel's native function features.

1. Intellisense: As we type the function name, Excel provides suggestions based on similar functions. Excel supports this for 5g functions, but only in the desktop version of Office 365.
2. Tooltips: As we type a 5g function's name, Excel displays a tooltip providing a quick overview of the function's purpose. This is possible thanks to the /** */ comment type shown in the source below.
3. Inline Help: If we omit an argument, Excel will provide inline help. The inline help source is shown below. It is one long text string that TEXTSPLIT() will divide into rows and columns which, when needed, displays in Excel's grid.
4. Argument Flexibility: The function offers flexibility with its optional OpeningValues argument, allowing you to include initial values for each row.

How RunTotRowsλ() Works

At the core of RunTotRowsλ() are two key Excel functions: REDUCE() and SCAN().

• REDUCE(): Iterates through each row of the Values array. SEQUENCE() keeps track of iterations. REDUCE() passes each number from SEQUENCE() to a LAMBDA() which renames that sequence number: n.
• SCAN(): Calculates the running total for each row. CHOOSEROWS() uses n to select the specific row from the Values argument. INDEX() retrieves the corresponding value from the OpeningValues argument.

After SCAN() creates the running totals for a row, RunTotRowsλ() stacks that row below any rows SCAN() has already processed which are stored in REDUCE()s accumulator (Acc).

UPDATE#1!

Diarmuid Early posted an alternative to the REDUCE/xSTACK procedure. Below is his approach. It is significantly faster in this application. His approach applies SCAN to the entire 2d array. That causes the second row to start at the first row's last value. That isn't exactly what we want. To compensate, he subtracts each previous (above) row's last value from the next (below) row's values. He does this in one step by taking the last column from the SCANned result, shoving that column down one row, and subtracting that column from the entire array. To shove the column down one row, he places a zero on top of it (using VSTACK) and removes the now extra row from the column's bottom (using DROP).

NOTE! Diarmuid used an ETA function in his approach which, I believe, is not yet available to everyone so I reverted it back to a non-ETA function.

UPDATE#2!

Peter Bartholomew posted his solution. He processes each row individually in the LET Step named Function. But note that that the LAMBDA in that LET step is not passed any arguments. The argument it needs is Idx, but it hasn't been declared anywhere or passed to the LAMBDA. That LET step will not do anything until MAPλ provides row numbers, one by one, to this function. MAPλ is pure genius, and difficult to understand so I will let Peter provide any explanation on how it works.

Which approach is best?

The REDUCE/xSTACK approach is more straight forward, but for this application, the slowest. The difference is noticeable at around a 1000+ rows.

Peter Bartholomew 's is 10 times faster but more complex because it requires an external helper function. This helper function is what makes it fast. It is a major improvement for models needing to crunch a relatively large number of rows and well worth implementing when the application fits.

Diarmuid Early 's approach uses a clever fix to the SCAN-it-all tactic. It is simple and, more importantly, 10 times faster than Peter's. Simplicity and speed makes it the clear winner for this application.

Try it Yourself!

RunTotRowsλ() is a part of BXL's Array Essentials library available for download using the Advanced Formula Environment (AFE). AFE is part of Microsoft's free Excel Labs add-in.

Need help getting or using AFE? See: https://www.youtube.com/watch?v=2SEYLHzyw3k

The URL for BXL's Array Essentials Library is: https://gist.github.com/CHatmaker/b5bb5e364fbfc90032c48d5f886d82b0