Mar 13 2023 10:48 AM
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?
Mar 13 2023 11:12 AM
=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?
Mar 13 2023 12:59 PM
And this
=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)))))