How to dynamically fill formula down into spill range

Copper Contributor

I have a complex formula that looks for the maximum value in column L over a set of previous rows, provided the dates associated with those previous rows are within 33 days prior to the date associated with the current row (column B), and also provided that the previous rows represent data from the same site as the current rows' site (column A).  The formula is as follows:

=MAXIFS(L$2:L2,B$2:B2,">"&B3-33,A$2:A2,"="&A3)

 

I would like to propagate this formula down dynamically based on how much data user enters into a table - that is, based on the spill range for column A.  I tried using an IF clause of the order of IF(INDEX(A2#<>"",my_formula,"") but that ends up spilling the formula from a single row, without dynamically changing the reference row each time.

 

Any ideas how to fix this?

 

2 Replies

@APK99 

=MAP(INDIRECT("A2:A"&COUNTA(A:A)+1),INDIRECT("B2:B"&COUNTA(A:A)+1),INDIRECT("L2:L"&COUNTA(A:A)+1),LAMBDA(colA,colB,colL,MAXIFS(L$2:colL,B$2:colB,">"&OFFSET(colB,1,0)-33,A$2:colA,"="&OFFSET(colA,1,0))))

Does this return the expected result?

dynamically fill formula down.JPG 

 

And this

2023-03-13 20_56_04-Classeur3 (version 2).xlsx - Excel.png

 

=MAP(L2#, LAMBDA(R, MAX(OFFSET(L2, 0, 0, (ROW(R) - 1)) * (OFFSET(B2, 0, 0, (ROW(R) - 1)) > (OFFSET(B1, ROW(R),0) - 33)) * (OFFSET(A2,  (ROW(R) - 2),0) = OFFSET(A1, ROW(R),0)))))

 

 

 

@APK99