Need to refer to previous rows and columns in spill range

[Joe_S]

Below is my spill range formula. Variable "result" in the LET is populating the cells. I am struggling to define variables prevAmtRows and PrevAmtColumns which are assigned a value of 999.

What l need PrevAmtRows to do is to calculate the values returned by result in the previous rows of the column. Row 1 is always zero. For example, if rows 1,2, and 3 of column 1 have a value of 100 returned by result, row 4 should return 300 for variable prevAmtRows. PrevAmtColumns is the same concept in that it should calculate the sum of result on the row up to the previous column with column 1 always being zero.

I've read about recursive lambdas and DROP and SCAN function, but still cannot figure it out. This is my final step to complete my task.

Please note that the formula below is in a spill range.


=MAKEARRAY(EndYear-StartYear+1, EndYear-StartYear+1,
LAMBDA(r,c,

LET(
cyLoss, INDEX(MyLosses,r),
cyIncome, INDEX(myIncome,c),
PrevAmtRows, 999
PrevAmtColumns, 999

result, MIN(cyLoss-PrevAmtRows, MAX(0,cyIncome)-PrevAmtColumns),
result)
)

 

[Cubist]

It would be helpful if you could post sample data and you're expected result.