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

[TheDub]

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

Situation 1: you have a column of numerical values; for each row in the column, you need to determine if the current row is higher( lower) than values in all previous rows.

Use case -  if the current row is a new MAX()/MIN(), one action is taken (see next situation), otherwise nothing.

Example:

Current Value	Expected Output
10	New Max! <---this being the 1st row, it’s a new max by definition
8	Same Old
9	Same Old
12	New Max!
11	Same Old

Situation 2: you have two numerical columns. In Column 1 (by operation of other parts of the sheet), you will have either a 0 (zero) or a value which is higher than the sum/running total of all previous Column 2 values. The function needs to output a Column 2 value for the current row which is either 0 (if the current Column 1 row value is 0) or the current Column 1 value less the running total of all previous Column 2 values.  

Use case – say sales commissions are only paid when a new high sales figure is achieved (see Situation 1).  If triggered, the commission amount (determined elsewhere) – less (i.e., after credit for) all previous commissions paid – is now payable; otherwise – the current commission amount is 0.

Example:

Current Value	Expected Output
10	10 <---this being the 1st row, it’s a new high by definition
0	0
0	0
12	2
15	3

Both can obviously be achieved “manually” but I’m looking for a dynamic function (most recent version of Excel Insiders/beta). The data can be either in a range or table (whichever makes the function “cleaner”), but should allow the user to either add a row, or delete an existing row, without affecting the integrity of the calculation.

[#2 · Accepted solution]

@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

[#3]

@PeterBartholomew1 

As you suggest, the second calculation is virtually identical.

= LET(
    currentMax, SCAN(0, sales, MAX),
    priorMax,   DROP(VSTACK(0, currentMax), -1),
    action?,    MAP(currentMax > priorMax, priorMax > 0, AND),
    HSTACK(currentMax, IF(action?, "Calculate Bonus", ""))
  )

[#4]

@PeterBartholomew1 

As variant

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

[#5]

@TheDub 

=LET(rng, A1:A13,

currentMax,

SCAN(0, rng, LAMBDA(ini,arr,MAX(ini,arr))),

stackcurrentMax,

VSTACK(0,currentMax),

IF(DROP(MAP(currentMax,stackcurrentMax,LAMBDA(a,b,a>b)),-1),

currentMax-DROP(VSTACK(0, currentMax), -1),

0)

)

This returns the commission amount that is now payable in my sheet.

[#6]

@SergeiBaklan 

Don't spoil it for me Sergei !  I don't get that many opportunities to show off an Excel 365 insiders beta solution.

[#7]

Good morning all and sorry for the delay. I was trying out the solutions - both to really understand them and to see how they work with real world data. I accepted @PeterBartholomew1 first reply, since it was the first and used the same idea for the second situation (as did @OliverScheurich in his).

Some observations - the basic approach seems to be (i) clone the existing array, (ii) push it down (I'm using old-style stack push/pop terminology here...) by adding a 0 on top, and (iii) lop off the bottom of the new stack. You up with the with two arrays/stacks - with the 2nd shifted down one position from the first.

Good solution and it works. But like other situations with modern day Excel, it feels more like a workaround. In real life, I have to use it twice - once to determine the max() of the previous rows of one column and once again to determine the running total of the previous rows of another. Then make adjustments to eliminate potential undesired results (negative numbers, for example). I won't bore everyone with the details but at the end, after a lot of tinkering, it works but looks ugly.

(As an aside, I tried to see how this would be resolved with python - that solutions seems a little cleaner (and in the process learned about "float('-inf')" which was entirely new to me). If I have the time and brain power, I'll try to use it with a python-in-Excel approach and, if successful, post it here for everyone's edification!).

Final note - although not substantively similar, this experience had, for me, the same feel as the one generated by the trials and tribulations with the "array-of-arrays" quagmire. The powers-that-be behind Excel really need to do something about the look-and-feel of these solutions. Unlike, say, python, where you're rarely (if ever) expected to share the source code with others, Excel has a strong social aspect to it - you usually need to share the "source code" internally and sometimes externally; it's hard to believe I can explain this to anyone else (even if fairly Excel-savvy); they'll either have to take my word for it, or demand that it be redone "the old fashioned way".

OK, I'm off the soapbox. Thanks everyone for your suggestions.

[#8]

@TheDub 

Hi. Please don't even consider getting off the soapbox!!!  Hold on while a get you a megaphone.  The more well-informed users like you can get together with the message to Microsoft that they fouled up big-time in specifying the functionality, the better the chance of getting them to fund the correction.  I would want it even if it meant yet another rewrite of the calc engine and abandoning backward compatibility; it it that important.  OK, so that is not realistic to serve the needs of a small minority of Excel users, but I really hope something is possible.

It seems that as soon as Excel knows it is handling an array (TYPE 64) it fails to recognise the content as being Lambda functions (TYPE 128).  As the scope of my Lambda functions grows, with one function per worksheet being a possible end point, the more times I hit array-of-array problems within any given formula.  The workarounds are, frankly, ugly and make it a hard sell that, what is otherwise superb functionality, should be used consistently for all Excel solutions.

[#9]

@TheDub 

Back on topic.

At first sight one might expect both ACCUMULATE and DIFFERENCE to be implemented by similar stack operations as one another.  After all they are just a pair of inverse operations.  However, for ACCUMULATE, you need the running total and the current value, which is precisely what SCAN provides.  DIFFERENCE however requires the current value and the prior value at each step.  That would need both the prior value and the prior difference to be available on the stack.  When the calculation is complete the prior values can be discarded but, meanwhile, the two variables form an array .

The solution I adopted was to move away from the stack and offset the entire array, in order to return the differences as a single array calculation.  The offset step is somewhat ugly but it is effective.  Things get worse when you want to evaluate convolutions, even with a small kernel.  There, for depreciation or moving average calculation, one requires a small array of prior values to be retained, along with the newly calculated value.  Again, there are a number of workarounds, but one should not have to be thinking of workarounds for such frequently-arising process steps.

[#10]

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

[#11]

As the kids say: I can't even! Let me take it for a spin and see if I survive.