Forum Discussion
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 Accounting firms model depr for project finance by adding all capital purchases to a depreciation base which is an accumulation of all purchases for that item for the duration of it's useful life.
ie Buy 100 in Jan 2022 for life of 3 years. Add another 200 in Jan 2023 and another 250 in Jan 2024.
In Jan 2022 the Depr base will be zero (There is usually a 1 period offset and this model will be in months not years but using years for the example to keep the column count short.
In Jan 2023 the Depr Base will be the 100 from Jan 22 making 100
In Jan 2024 it will be the sum of the 100 from 2022 plus the 200 from 2023 making 300
In Jan 2025 it will be the sum of all three making 550
In Jan 2026 (assuming no more purchases it will then be the 200 from 2023 and the 250 from 2024 making 450. The initial 100 from 2022 will have expired.
The attached file gives a clearer picture.
I have a solution to do this row by row but need it in a block - see the file
I can't figure out how to achieve this and would much appreciate some Genius help!!
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!
4 Replies
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!
As always David you have risen to the challenge and presented a host of amazing solutions. What I love about all your solutions is that they are slick, fast and easy to implement and adapt. They just slot in and work. Brilliant. I have used the first one Map-Take. I will play with the others in a few days when time. The others may enable more flexibility with Periods and intervals etc but I don't need that here as all the periods are monthly. The roll up to other timings is done later.
I adapted it to fit my scenario better and pushed the offset with the end result slotting in a blank row at the start. Now all my block extensions and reductions with my generic VBA will all work. There are multiple blocks of entities as everything is done in these blocks so I have added generic code to expand or contract these blocks depending on the number of entities but that requires all the blocks to be fully dynamic which they now are.
Many thanks
- 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)
- 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)
- 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)
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
- Set up your data:
