Forum Discussion

APK99's avatar
APK99
Copper Contributor
Mar 13, 2023

How to dynamically fill formula down into spill range

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?

 

excel
Formulas and Functions

2 Replies

Sort By
  • Hecatonchire's avatar
    Hecatonchire
    Iron Contributor
    Mar 13, 2023

     

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

     

     

     

    APK99 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 13, 2023

    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?

     

Resources