Referring to PREVIOUS rows only within BYROW() (or any other function)

Apologies if this has been addressed previously, but I couldn’t find an answer. I believe the issue is the same for both situations below – which is the reason why I didn’t post two separate question...

excel

Formulas and Functions

PeterBartholomew1

May 01, 2024

TheDub

The first part might be

= LET(
    currentMax, SCAN(0, currentValue, MAX),
    priorMax,   DROP(VSTACK(0, currentMax), -1),
    action?,    currentMax > priorMax,
    HSTACK(currentMax, IF(action?, "New Max!", "Same old"))
  )

giving

PeterBartholomew1

Silver Contributor

May 02, 2024

TheDub

Just to throw another variant into the mix

= LET(
→1, "Define Lambda function",
    Stepλ, LAMBDA(val,
      LET(
        k, SEQUENCE(COUNT(val)),
        u, INDEX(val, k-1),
        v, INDEX(val, k),
        IF(v>u, v-u, "")
      )
    ),
→2, "Apply functions",
    runningMax, SCAN(0, value, MAX),
    HSTACK(runningMax, Stepλ(runningMax))
  )

TheDub
Iron Contributor
May 03, 2024
As the kids say: I can't even! Let me take it for a spin and see if I survive.

Forum Discussion

Referring to PREVIOUS rows only within BYROW() (or any other function)

Resources