Forum Discussion

Brass Contributor

Oct 21, 2024

Solved

Return of the Corkscrew with Spilled Arrays

I'm starting a new topic but do want to refer to PeterBartholomew1 post on accumulators here https://techcommunity.microsoft.com/t5/excel/ways-of-performing-accumulation-with-dynamic-arrays/m-p/...

Issue08.xlsx19 KB

excel

Formulas and Functions

djclements

Oct 23, 2024

James_Buist Just for good measure, MMULT can also get the job done with minimal effort.

Closing Balance (cell G22):

=LET(
    data, HSTACK(F22:F24,G12:M14+G17:M19),
    cId, SEQUENCE(,COLUMNS(data)),
    arr, MMULT(--data,--(DROP(cId,,1)>=TOCOL(cId))),
    VSTACK(arr,BYCOL(arr,SUM))
)

Cheers!

djclements

Bronze Contributor

Oct 23, 2024

James_Buist Just for good measure, MMULT can also get the job done with minimal effort.

Closing Balance (cell G22):

=LET(
    data, HSTACK(F22:F24,G12:M14+G17:M19),
    cId, SEQUENCE(,COLUMNS(data)),
    arr, MMULT(--data,--(DROP(cId,,1)>=TOCOL(cId))),
    VSTACK(arr,BYCOL(arr,SUM))
)

Cheers!

James_Buist

Brass Contributor

Oct 28, 2024

djclements

Replied to your PM but it said I had used up my quota so it never went through. The teams invite bounced too. I'm only brass so perhaps my PM limit is tiny!! I'm also on imesssage and whatsapp or can send invite to another email address. Not sure when I can pm again but can receive. Zoom is also fine if you have it. time suggested works well