Forum Discussion

boukasa's avatar
boukasa
Brass Contributor
Jan 01, 2024

Running total with reset, lambda

I use RunTotCols below to do a running total on a column:   EatCols = LAMBDA(arr, LET(rows, ROWS(arr), res, N(SEQUENCE(, rows) >= SEQUENCE(rows)), res)); // Converts an array's columns into a run...
excel
Formulas and Functions
Rodrigo_'s avatar
Rodrigo_
Iron Contributor
Jan 02, 2024

Hello boukasa 
Try this example:

RunTotColsWithReset = LAMBDA(arr, labels,
    LET(
        rows, ROWS(arr),
        cols, COLUMNS(arr),
        resetFlags, Reset(labels),
        prevResetFlags, PushRows({0}, resetFlags),
        resetStarts, IF(resetFlags > prevResetFlags, SEQUENCE(rows), ""),
        resetGroups, COUNTA(FILTER(resetStarts, resetStarts <> "")),
        resetIndices, MMULT((SEQUENCE(rows) >= TRANSPOSE(resetStarts))*(SEQUENCE(rows, cols, 1, 0) = 0), SEQUENCE(resetGroups)),
        res, IFERROR(XLOOKUP(resetIndices, SEQUENCE(resetGroups), RunTotCols(arr))),
        res
    )
);

This function first calculates the reset flags for the label column using your Reset function. It then determines where each reset starts and groups the resets together. For each group, it calculates the running total using your RunTotCols function. The XLOOKUP function is then used to map these running totals back to the original array.

Note that this is just only a suggestion, and you might need to adjust it accordingly based on your specific needs. Also, this function assumes that your RunTotCols and Reset functions work as expected. 

Resources