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!

djclements's avatar
djclements
Bronze Contributor
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!

James_Buist's avatar
James_Buist
Copper Contributor
Nov 25, 2024

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

Resources