# Spill a range that is expanding

#### Haui

##### Board Regular
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Eric W

##### MrExcel MVP
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
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
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
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.

Replies
3
Views
281
Replies
2
Views
1K
Replies
12
Views
574
Replies
4
Views
129
Replies
8
Views
364

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,772
Messages
5,766,397
Members
425,350
Latest member
procha

### 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.

### Which adblocker are you using?

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

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