Spill a range that is expanding

Haui

Board Regular
Joined
Apr 5, 2009
Messages
209
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,683
Nothing super elegant, but here are 2 options:

Book2
ABC
1111
2233
3366
Sheet9
Cell Formulas
RangeFormula
A1:A3A1=SEQUENCE(3)
B1:B3B1=MMULT(IF(SEQUENCE(ROWS(A1#))>=SEQUENCE(,ROWS(A1#)),TRANSPOSE(A1#),0),SEQUENCE(ROWS(A1#),,,0))
C1:C3C1=SUBTOTAL(9,OFFSET(A1#,0,0,SEQUENCE(ROWS(A1#))))
Dynamic array formulas.
 

Haui

Board Regular
Joined
Apr 5, 2009
Messages
209
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,683
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

Board Regular
Joined
Apr 5, 2009
Messages
209
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.
 

Forum statistics

Threads
1,148,157
Messages
5,745,105
Members
423,923
Latest member
yuvaraj859

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top