Spill a range that is expanding

[Haui]

Hi, is there a way to have a formula spill a range that is expanding?

Say, the range A1:A3 is populated by =SEQUENCE(3) and now want a formula in C1 that spills into C1:C3 in the following way:
C1 = range A1
C2 = range A1:A2
C3 = range A1:A3
and be able to work with these ranges by wrapping other functions around them in the C1 formula that spills down.

e.g. an expanding sum range that is not involving a spill formula would be:
=SUM(A$1:A1) and then I drag that formula down.

But how to replicate this result with a spill function that uses A1#?

Hope it's clear what I am trying to do.

Any ideas much appreciated.

Thanks

 

[Eric W]

Nothing super elegant, but here are 2 options:

Book2
	A	B	C
1	1	1	1
2	2	3	3
3	3	6	6
Sheet9

Cell Formulas
Range	Formula
A1:A3	A1	=SEQUENCE(3)
B1:B3	B1	=MMULT(IF(SEQUENCE(ROWS(A1#))>=SEQUENCE(,ROWS(A1#)),TRANSPOSE(A1#),0),SEQUENCE(ROWS(A1#),,,0))
C1:C3	C1	=SUBTOTAL(9,OFFSET(A1#,0,0,SEQUENCE(ROWS(A1#))))
Dynamic array formulas.

 

[Haui]

Thanks, Eric W.

Can you explain to me please why the SUBTOTAL instead of a normal SUM function works?
I am a bit puzzled because the SUBTOTAL function evaluates (correctly) to: {1;3;6}
However, the OFFSET stand-alone part evaluates to: {1;1;1} - this doesn't seem to make sense to me?

Props on the MMULT solution. I feared that passing back ranges to continue calculating with them might involve cumbersome matrix multiplications.

Lastly, if there's an OFFSET-solution, shouldn't there also be a non-volatile INDEX-solution as it can also pass back ranges?
The range OFFSET(A1#,,,SEQUENCE(ROWS(A1#)) should be similar to: A1#:INDEX(A1#,SEQUENCE(ROWS(A1#))) but doesn't seem to work.
This gives a #VALUE! error but in-cell evaluates to: {1;2;3} which strangely stays an error even after wrapping e.g. SUM around it.

Thanks and Regards

 

[Eric W]

Can you explain to me please why the SUBTOTAL instead of a normal SUM function works?

Um, no? All I know is that it works. Using reference functions inside array formulas usually doesn't work. There are a few exceptions. OFFSET works inside SUBTOTAL inside of an array formula. If you step through the formula using the Evaluate Function tool, it shows #VALUE when the OFFSET is evaluated, but when you step to the SUBTOTAL, it calculates correctly. I don't know why, it has to do with how the functions are internally designed. OFFSET also works inside of COUNTIF (SUMIF, etc.) inside of an array formula, so that gives you a few more options.

Props on the MMULT solution. I feared that passing back ranges to continue calculating with them might involve cumbersome matrix multiplications.

Yep, exactly. The idea is to create a triangular array and sum the columns. But it's complicated and not at all intuitive or easy to understand.

Lastly, if there's an OFFSET-solution, shouldn't there also be a non-volatile INDEX-solution as it can also pass back ranges?

You'd think so, but apparently not. Again, it depends on how the functions are designed internally. With Excel 365, some things were changed, and INDEX does work inside of array formulas, but in specific cases. It still doesn't work in this case. I played around with it a bit to see if I could get it to work, but still no dice.

Hope this helps some, but I realize it still leaves some questions unanswered.

 

[Haui]

No worries, I definitely learnt something today.

I think I will not go down the route of MMULT as any other operations beyond simple summing up (like multiplication or summing and multiplying combined), seem to create mind-boggling matrix operations quickly.

Thanks a lot.