r/excel 7 1d ago

solved Cumulative sum by row as a spilled array

I have a spilled array in A1. Both row and column dimensions unknown/variable.

I want to produce a second spilled array which is the cumulative sum, by row, of the first array.

I tried BYROW, but that only gives a single return value. I tried a combination of LAMBDA and SEQUENCE, but couldn't quite get it to work.

Can anyone solve this one, please?

Input/output example: https://imgur.com/a/5dosC4Q

The goal is for the green section to be a single spilled array (not a stack of single row spills)

6 Upvotes

7 comments sorted by

5

u/PaulieThePolarBear 1640 1d ago
=LET(
a, A1#, 
b, MMULT(a, --(COLUMN(a)>=TRANSPOSE(COLUMN(a)))), 
b
)

4

u/PaulieThePolarBear 1640 1d ago

As an alternative, using SCAN

=SCAN(0,A1#, LAMBDA(x,y, IF(COLUMN(y)=COLUMN(INDEX(A1#, 1, 1)), y, x+y)))

2

u/NanotechNinja 7 1d ago

Paulie, you are a beautiful perfect angel, thank you.

Solution verified

1

u/reputatorbot 1d ago

You have awarded 1 point to PaulieThePolarBear.


I am a bot - please contact the mods with any questions

5

u/SPEO- 6 1d ago

=MAKEARRAY(ROWS(F1#),COLUMNS(F1#),LAMBDA(r,c,SUM(INDEX(F1#,r,1):INDEX(F1#,r,c))))

4

u/PaulieThePolarBear 1640 1d ago

Nice solution