Making LET spill

Question

I have the current formula

=LET(
    rad, ROW(C2),
    sumArea, SUM(INDIRECT(ADDRESS(rad,3)):INDIRECT(ADDRESS(rad+5,3))),
    result, sumArea/INDIRECT(ADDRESS(rad+6,2)),
    result
)

which calculates a five day change from the value in cell B7 with the change column C. It works perfectly when just giving the ROW(C2) argument, but I ideally want it to work with spill, since the C column is itself spilled. When I give the argument ROW(C2#), it does spill, but returns a value error in each of the spilled results.

Is it not possible to use the LET function this way, or can I change something to make it work? I'm thankful for any guidance I can get! :D

---

Sample data:

The value -28.6% is calculated by taking SUM(-2,2,2,-5,-1)/14.

Answer 1

As others have suggested, using INDIRECT and ADDRESS is not generally a good idea (not least as the functions are volatile so will recalculate every time the sheet changes).

With the formula in cell B3

=LET(a,A3:A9,o,B1,n,SEQUENCE(ROWS(a)-o),e,INDEX(a,n),s,INDEX(a,n+o),(e-s)/s)

Where B1 is the variable lookback (eg 5 days)

If you have too much time on your hands, or think you might want to re-use this formula, create a Name via the Formulas / Define Name menu:

And then the formula in B3 is now:

=PeriodChange(A3:A9,B1)

eg: if you still wanted the helper column of 1-period changes:

=PeriodChange(A3:A9,1)

EDIT:

This alternative formula is a little more flexible, as it allows you to pass in an array with more than one column, using CHOOSEROWS rather than INDEX:

=LET(a,A3:B9,o,B1,n,SEQUENCE(ROWS(a)-o),i,LAMBDA(m,CHOOSEROWS(a,m)),e,i(n),s,i(n+o),(e-s)/s)

Using a LAMBDA alias for the CHOOSEROWS function cuts down the typing.

Answer 2

Thanks to the very helpful comment I got from @Harun24hr, I managed to solve this. My revised method is to create a lambda function that contains the LET function that works when having a single cell input, and then using BYROW to perform the calculation over C2#.

The final formula looks like this:

=BYROW(C2#,
     LAMBDA(array,
             LET(
             rad, ROW(array),
             sumArea, SUM(INDIRECT(ADDRESS(rad,3)):INDIRECT(ADDRESS(rad+5,3))),
             result, sumArea/INDIRECT(ADDRESS(rad+6,2)),
             result
             )
     )
)

I still don't know whether the LET function can be reliably spilled but this workaroud solves the problem of it not spilling correctly in my case.

Answer 3

This is a solution w/o helper column for delta and w/o indirect:

=LET(dataBase,B2:B8,
dataDelta,dataBase - DROP(dataBase,1),
BYROW(dataBase,LAMBDA(r,
           LET(rowR,ROW(r),
                  sumArea,SUM(FILTER(dataDelta,(ROW(dataBase)>=rowR)*(ROW(dataBase)<=rowR+4),0)),
                  IFNA(sumArea/INDEX(dataBase,rowR+4),"")))))

Answer 4

Looking at the data it seems you can just use:

Formula in B1:

=LET(r,A1:A7,d,DROP(r,5),(DROP(r,-5)-d)/d)

Obviously A1:A7 is a placeholder for your initial data, not the helper column.