Forum Discussion

James_Buist's avatar
James_Buist
Copper Contributor
Nov 24, 2024
Solved

Dynamic Arrays - Changing a row by row Spilled Array to a block - Replacing Sumifs?

I have already had some help putting this together as a stop gap solution but need a more permanent one. File attached explains most. But I'm building a Depreciation Base which is how most Accountin...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Nov 25, 2024

    Hi James, a couple of possibilities could be:

    =LET(
    data, F6:L8,
    yrs, D6:D8,
    one, INDEX(data, 1, 1),
    MAP(data, IF({1}, yrs, data), LAMBDA(v,n, SUM(TAKE(one:v, -1, -n))))
)
    =LET(
    data, F6:L8,
    yrs, D6:D8,
    rwsλ, BYROW(data, LAMBDA(r, LAMBDA(r))),
    cols, SEQUENCE(, COLUMNS(data)),
    MAP(IF({1}, rwsλ, data), IF({1}, cols, data), IF({1}, yrs, data), LAMBDA(r,c,n, SUM(TAKE(TAKE(r(),, c),, -n))))
)

    I'm not too sure which is more efficient, so I'll leave the speed testing to you. The attached file also includes a couple of SUMIFS options. Cheers!

Kidd_Ip's avatar
Kidd_Ip
MVP
Nov 25, 2024
  1. Set up your data:
    • Column A: Purchase Dates (e.g., Jan 2022, Jan 2023, Jan 2024)
    • Column B: Purchase Amounts (e.g., 100, 200, 250)
  2. Create a helper column to calculate the end of the useful life for each purchase:
    • In Column D, calculate the end date of the useful life for each purchase. Assuming the useful life is 3 years, you can use the following formula in D2:=EDATE(A2, 36)
  3. Calculate the depreciation base:
    • In Column C, use the following formula to sum the purchases that are still within their useful life:=SUMIFS($B$2:$B$100, $A$2:$A$100, "<=" & A2, $D$2:$D$100, ">" & A2)
  • James_Buist's avatar
    James_Buist
    Copper Contributor
    Nov 25, 2024

    I'm looking to do this all in a single block. I already have a solution to do this row by row but my data is in blocks so I need to have it in a sigal block of multiple rows with a single function

Resources