Update Equally Distributed Formula

[Pestomania]

Cell V2 has a formula that does an equal distribution but I would like to make it to where I do not have to add it to every January month field.

As you can see in V3, AH3, and so on, I have to paste the distribution formula into the field. I'd like the formula to auto-fill the table based on the value found in the A1:G26 table.

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1	Program	'23	'24	'25	'26	'27	'28								Batch Size							Jan '23	Feb '23	Mar '23	Apr '23	May '23	Jun '23	Jul '23	Aug '23	Sep '23	Oct '23	Nov '23	Dec '23	Jan '24	Feb '24	Mar '24	Apr '24	May '24	Jun '24	Jul '24	Aug '24	Sep '24	Oct '24	Nov '24	Dec '24
2	Program 1	80	52	52	60	88	32							Program 1	4						Program 1	4	8	8	4	8	8	4	8	8	4	8	8	4	8	8	4	8	8	4	8	8	4	8	8
3	Program 2	16	20	20	1	3	25							Program 2	1						Program 2	1	1	2	1	1	2	1	1	2	1	1	2	1	2	2	1	2	2	1	2	2	1	2	2
4	Program 3	289	238	374	391	204	272							Program 3	17						Program 3	17	17	34	17	34	17	17	34	17	34	17	34	17	17	17	17	17	34	17	17	17	17	17	34
5	Program 4	16	22	18	14	42	44							Program 4	2						Program 4	0	2	2	0	2	2	0	2	2	0	2	2	0	2	2	2	2	2	2	2	2	2	2	2
6	Program 5	125	50	10	90	125	15							Program 5	5						Program 5	10	10	10	10	10	10	10	10	10	10	10	15	0	5	5	5	5	5	0	5	5	5	5	5
7	Program 6	105	105	15	165	360	240							Program 6	15						Program 6	0	15	0	15	0	15	15	0	15	0	15	15	0	15	0	15	0	15	15	0	15	0	15	15
8	Program 7	250	40	60	160	120	180							Program 7	10						Program 7	20	20	20	20	20	20	20	20	20	20	20	30	0	0	10	0	0	10	0	0	10	0	0	10
Sheet1

Cell Formulas
Range	Formula
U2:U26	U2	=UNIQUE(A2:A26)
V1:CO1	V1	=TEXT(DATE("20"&RIGHT(B1,2),SEQUENCE(,12*COUNTIF(A1:G1,"<>'")-12),1),"mmm 'yy")
V2:CO2	V2	=INDEX(LET(z,SUMPRODUCT(($A$2:$A$26=$U2)*($B$1:$G$1=RIGHT(V$1,3))*$B$2:$G$26)/VLOOKUP($U2,$N$2:$O$26,2,FALSE),b,SEQUENCE(1,12*COUNTIF($A$1:$G$1,"<>'")-12),c,z/12*b,d,INT(z/12)*SEQUENCE(1,12*COUNTIF($A$1:$G$1,"<>'")-12,1,0),e,MOD(c,1),f,IF(INDEX(e,b)<INDEX(e,b-1),d+1,d),f)*VLOOKUP($U2,$N$2:$O$26,2,FALSE),MOD(SEQUENCE(1,12*COUNTIF($A$1:$G$1,"<>'")-12,0),12)+1)
V3:AS8	V3	=INDEX(LET(z,SUMPRODUCT(($A$2:$A$26=$U3)*($B$1:$G$1=RIGHT(V$1,3))*$B$2:$G$26)/VLOOKUP($U3,$N$2:$O$26,2,FALSE),b,SEQUENCE(1,12),c,z/12*b,d,INT(z/12)*SEQUENCE(1,12,1,0),e,MOD(c,1),f,IF(INDEX(e,b)<INDEX(e,b-1),d+1,d),f)*VLOOKUP($U3,$N$2:$O$26,2,FALSE),MOD(SEQUENCE(1,12,0),12)+1)
Dynamic array formulas.

 

[felixstraube]

Where do you get a spill error?
Can you maybe upload the file without your data or with random sample data?
Trying to solve it like this is a bit difficult.

 

[Pestomania]

Where do you get a spill error?
Can you maybe upload the file without your data or with random sample data?
Trying to solve it like this is a bit difficult.

Hi, you're absolutely right.

I have created a file that I think will help?

Loading Google Sheets

Let me know if there are any problems.

 

[felixstraube]

I will need you to grant me access.

 

[Pestomania]

I will need you to grant me access.

Should be good. I thought I set the permissions right.

 

[Pestomania]

That works amazing!!

Only question:

=CHOOSEROWS(UNIQUE(Dashboard[Program]),ROWS($N$2:$N2))

What is the Rows($N$2:$N2)? Those cells are empty from what I can tell.

 

[felixstraube]

The formula

=ROWS($N$2:$N2)

gives you a sequence of numbers starting from 1 and incrementing as you drag down:

Prestons Playground for Modeling Felix.xlsx
	H	I
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	6	6
8	7	7
Data Tables

Cell Formulas
Range	Formula
H2:H8	H2	=ROWS($H$2:$H2)
I2:I8	I2	=ROWS($N$2:$N2)

Since Dynamic formulas that return more than one array item are incompatible with Tables we use this to filter the result from the UNIQUE function

You could use any column, it doesn't matter.

 

[Pestomania]

The formula

=ROWS($N$2:$N2)

gives you a sequence of numbers starting from 1 and incrementing as you drag down:

Prestons Playground for Modeling Felix.xlsx
	H	I
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	6	6
8	7	7
Data Tables

Cell Formulas
Range	Formula
H2:H8	H2	=ROWS($H$2:$H2)
I2:I8	I2	=ROWS($N$2:$N2)

Since Dynamic formulas that return more than one array item are incompatible with Tables we use this to filter the result from the UNIQUE function

Oh okay, cool! Thank you for the explanation. What happens if something is in N2 all of a sudden? I am planning to add numerous more data tables on this tab. Is that going to mess anything up? I tried testing a few different types of data, but wanted to check with the experts.

 

[felixstraube]

Nothing will happen, but to avoid reference problems (if you delete exactly that column) you could change that to the same column as Progam in you BatchSize table like this:

Prestons Playground for Modeling Felix.xlsx
	A	B
1	Program	Batch Size
2	Program 1	4
3	Program 2	1
4	Program 3	17
5	Program 4	2
6	Program 5	5
7	Program 6	15
8	Program 7	10
Data Tables

Cell Formulas
Range	Formula
A2:A8	A2	=CHOOSEROWS(UNIQUE(Dashboard[Program]),ROWS($A$2:$A2))

 

[Pestomania]

Nothing will happen, but to avoid reference problems (if you delete exactly that column) you could change that to the same column as Progam in you BatchSize table like this:

Prestons Playground for Modeling Felix.xlsx
	A	B
1	Program	Batch Size
2	Program 1	4
3	Program 2	1
4	Program 3	17
5	Program 4	2
6	Program 5	5
7	Program 6	15
8	Program 7	10
Data Tables

Cell Formulas
Range	Formula
A2:A8	A2	=CHOOSEROWS(UNIQUE(Dashboard[Program]),ROWS($A$2:$A2))

Oh awesome! So it doesn't so much care what is there, just that it has a row to reference!

Thank you very much and sorry for all of the confusion. This has been a massive project and I am not even 5% done but I think a lot of these formulas can help me piece together the rest of the excel workbook. In total, this workbook has over 200,000 calculations. I am trying to make it easier to manage.

 

[felixstraube]

Happy to help. Thanks for the feedback!