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 running total
RunTotCols = LAMBDA(arr, LET(res, TRANSPOSE(MMULT(TRANSPOSE(arr), EatCols(arr))), res));

 

And I use Reset below to determine when a column's value changes:

 

PushRows = LAMBDA(addarr, target,
    LET(
        newrowct, ROWS(addarr),
        oldrowct, ROWS(target),
        choparr, CHOOSEROWS(target, SEQUENCE(oldrowct - newrowct)),
        r, VSTACK(addarr, choparr),
        r
    )
);

Reset =LAMBDA(col,
  LET(
    prevcol, PushRows({""}, col),
    same, (col = prevcol) + (SEQUENCE(ROWS(col))=1),
    same
));

 

I need to combine these ideas so that I get a running total that resets when a corresponding label column changes, but how to do this escapes me. Any suggestions? Thanks so much.

excel
Formulas and Functions
  • Rodrigo_'s avatar
    Rodrigo_
    Steel 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