Forum Discussion

Iron Contributor

May 01, 2024

Solved

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 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

SergeiBaklan

Diamond Contributor

May 01, 2024

PeterBartholomew1

As variant

=SCAN(, currentValue, LAMBDA(a,v, IF(v>a,v,a) ) )

PeterBartholomew1
Silver Contributor
May 01, 2024
SergeiBaklan
Don't spoil it for me Sergei ! I don't get that many opportunities to show off an Excel 365 insiders beta solution.