need spill formula

[Scott R]

Rows 2:8 show inputs. Rows 10:14 show desired output--I'd like a dynamic array formula in B10 that spills to B10:C14 (two columns in this example but I really need to spill to 40+ columns).

B2: Item1 needs 10,000 of purchases.
B8: Total purchases are 50,000.
B10: 10,000 of the purchases are allocated to Item1, so the new balance for Item1 is 0, and there are 40,000 of purchases to allocate to Items 2, 3, and part of 4.
Repeat for each Month (column).

So it's basically Rows 2:6 minus Row 8 = Rows 10:14, where no values in 10:14 would be negative and allocation order would cascade from Item1 to Item5.

Book2
	A	B	C
1		Month1	Month2
2	Item1 begin bal	10,000	10,000
3	Item2 begin bal	11,000	23,000
4	Item3 begin bal	26,000	21,000
5	Item4 begin bal	7,000	11,000
6	Item5 begin bal	29,000	15,000
7
8	tot purchased	50,000	20,000
9
10	Item1 end bal	0	0
11	Item2 end bal	0	13,000
12	Item3 end bal	0	21,000
13	Item4 end bal	4,000	11,000
14	Item5 end bal	29,000	15,000
Sheet1

 

[Vogateer]

I'm afraid I can't follow what kind of calculations you want for B10:B14. You say B2 is 10,000, so B10 is 0, but why would B10 would be 0? What calculation determines that? Could you give us some explicit calculation examples, maybe for B10, B12, and B14?

 

[Scott R]

In Month1 there is a beginning balance of 83,000 in total (B2:B6).
Purchases are 50,000 (B8) and are applied to the beginning balance starting with Item1.
The resulting ending balance of 33,000 is in B10:B14.

 

[dreid1011]

I think your calculation for the positive amounts is not the same as the negative amounts.. no matter how I write the calc, I get 33,000 in B14... which makes sense for what the formula is doing.
Maybe once the amounts get positive, the calc needs to change a little bit..

Book1
	I	J	K
2	40000	10000	-40000
3	29000	21000	-29000
4	3000	47000	-3000
5	-4000	54000	4000
6	-33000	83000	33000
Sheet1

Cell Formulas
Range	Formula
I2:I6	I2	=$B$8-SUM($B$2:B2)
J2:J6	J2	=SUM($B$2:B2)
K2:K6	K2	=SUM($B$2:B2)-$B$8

 

[Vogateer]

That explanation gave me enough to figure it out. It's easy to get a formula for these cells, like this for B10:B14:

=LET(sum_input,SUM(B$2:B2), sum_output,SUM(B$9:B9), IF(sum_input>B$8, sum_input-B$8-sum_output, 0))

But that's not a spill formula. I'm not great with spill-type formulas, so I'm having trouble getting something to work.

 

[dreid1011]

In my attempt to solve this, I got close, but @Fluff ultimately provided the solution: Dynamic Array referencing previous row.

You'll just have to drag it across the months:

Book1
	A	B	C
1		Month1	Month2
2	Item1 begin bal	10000	10000
3	Item2 begin bal	11000	23000
4	Item3 begin bal	26000	21000
5	Item4 begin bal	7000	11000
6	Item5 begin bal	29000	15000
7
8	tot purchased	50000	20000
9
10	Item1 end bal	0	0
11	Item2 end bal	0	13000
12	Item3 end bal	0	21000
13	Item4 end bal	4000	11000
14	Item5 end bal	29000	15000
Sheet1

Cell Formulas
Range	Formula
B10:C14	B10	=MAP(-SCAN(B8,B2:B6,LAMBDA(a,b,a-b-MIN(0,a))),LAMBDA(m,MAX(0,m)))
Dynamic array formulas.

 

[Scott R]

Works great! Any way to have B10 spill to columns? I'd like it also process Column C and beyond.

 

[hagia_sofia]

Hello, just for fun (definitely use dreid1011's solution):

=LET(
a,B2:C6,
b,B8:C8,
array,VSTACK(a,b),
c,DROP(REDUCE("",SEQUENCE(COLUMNS(array)),LAMBDA(f,g,HSTACK(f,DROP(VSTACK(TAKE(CHOOSECOLS(VSTACK(array),g),-1),SCAN(TAKE(CHOOSECOLS(VSTACK(array),g),-1),DROP(CHOOSECOLS(VSTACK(array),g),-1),LAMBDA(x,y,IF(x-y<0,0,x-y)))),-1)))),,1),
IF(a-c<0,0,a-c))

 

[Vogateer]

Hello, just for fun (definitely use dreid1011's solution):

=LET(
a,B2:C6,
b,B8:C8,
array,VSTACK(a,b),
c,DROP(REDUCE("",SEQUENCE(COLUMNS(array)),LAMBDA(f,g,HSTACK(f,DROP(VSTACK(TAKE(CHOOSECOLS(VSTACK(array),g),-1),SCAN(TAKE(CHOOSECOLS(VSTACK(array),g),-1),DROP(CHOOSECOLS(VSTACK(array),g),-1),LAMBDA(x,y,IF(x-y<0,0,x-y)))),-1)))),,1),
IF(a-c<0,0,a-c))

I don't know how some of you are able to manage these formulas. It's hard for me to break them down.

 

[Scott R]

Here's what I landed on:
BYCOL spills right, VSTACK spills down (thx for the tip, Sofia!)

=LET( ary, B2:C6,     purch, B8:C8,
           DROP( REDUCE( "", SEQUENCE( ROWS( ary )), LAMBDA(a,b, VSTACK( a,
                           LET( prevCumul, IF( b = 1, SEQUENCE( , 2, 0, 0 ), BYCOL( TAKE( ary, b - 1 ), LAMBDA(c, SUM( c )))),
                                    bbal, CHOOSEROWS( ary, b ),
                                    ebal, IF( prevCumul > purch, bbal, bbal - purch + prevCumul ),
                                    IF( ebal < 0, 0, ebal ))))),
   1 ))