Spillable MAXIFS

Question

Good morning to everyone,

I have the following situation:

col i = date (array formula)
col l = profit (array formula)
col k = aggregate profit (array formula)
col z = running max for col i (between 0 and running max), stop and restart when date change.

Like this:

Before Excel introduced array formulas I had solved it with the following formula:

=MAX(0;MAXIFS($V$8:V8;$T$8:T8;I8)) 

with the vba completing the entire column.

After Excel introduced array formulas I had solved it with the following formula:

=MAP(i1#;k1#;LAMBDA(DATA;NET;MAX(0;MAXIFS($k$1:NET;$i$1:DATA;DATA))))

This solution works, it spill the result for the entire column and the calculations are correct, however on 10,000 lines it is slow.

The question I would like to ask to anyone who wants to answer is the following: is there a faster way, in Excel formula or VBA, to perform this operation?

Thank you all so much, I wish you a good day.

Dario

P.S. I read a lot of answer for MAXIFS in this community but there are no answer for "spill" MAXIFS. Thank you again

Answer 1

A Running Max: Spill MAXIFS Using SCAN Instead

=LET(ddata,I8:I27,aData,K8:K27,
    dc,ddata=VSTACK(INDEX(ddata,1)-1,DROP(ddata,-1)),
    SCAN(0,SEQUENCE(ROWS(ddata)),LAMBDA(sr,r,LET(
        a,INDEX(aData,r),
        IF(INDEX(dc,r),IF(a>sr,a,sr),IF(a<0,0,a))))))

Answer 2

Another formula:

=LAMBDA(dates, aggreg, DROP(REDUCE(0, UNIQUE(dates), 
   LAMBDA(a,b, VSTACK(a, SCAN(0,FILTER(aggreg, dates=b),
   LAMBDA(x,y,IF(y>x,y,x)))))),1))($J$5:$J$24,$K$5:$K$24)  

Answer 3

With sorted data, you could also use something like:

=LET(iData,I2:I15,kData,K2:K15,SCAN(0,SEQUENCE(ROWS(I2:I15)),LAMBDA(s,c,IF(c=1,MAX(s,INDEX(kData,c)),MAX(IF(INDEX(iData,c)=INDEX(iData,c-1),s,0),INDEX(kData,c))))))