Distributed Row Results

[Pestomania]

Hi! If I have an annual demand (let's say 100), how can I distribute that in a whole number pattern across 12 months?

What would this look like?

Annual Total	January	February	March	April	May	June	July	August	September	October	November	December
100	8	8	9	8	8	9	8	8	9	8	8	9

What if I want to distribute numerous totals?

Annual Total	January	February	March	April	May	June	July	August	September	October	November	December
110	9	9	10	9	9	9	9	9	10	9	9	9
50	4	5	4	4	4	4	4	4	4	5	4	4
Total	13	14	14	13	13	13	13	13	14	14	13	13

It will not always be an even number, but some form of distribution across 12 months.

 

[Pestomania]

No - i fixed something. It only worked right when the number in column A was a nice divisor of 12, but not too nice. (like 12). I changed it from the mod =0 to the mod being less than the last one - meaning it flipped over again.

Oh gotcha, cool beans!! Thanks for the help, I will implement this into our model.!

 

[Pestomania]

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Total	January	February	March	April	May	June	July	August	September	October	November	December
2	100	8	8	9	8	8	9	8	8	9	8	8	9	100
3	5	0	0	1	0	1	0	0	1	0	1	0	1	5
4	12	1	1	1	1	1	1	1	1	1	1	1	1	12
5	Total	9	9	11	9	10	10	9	10	10	10	9	11
Sheet24

Cell Formulas
Range	Formula
C1:M1	C1	=EOMONTH(B1,0)+1
N2:N4	N2	=SUM(B2#)
B2:M4	B2	=LET(z,A2,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)
B5:M5	B5	=B2#+B3#+B4#
Dynamic array formulas.

Is there a way to write in divisibles? So for Program_1, it needs to be divisible by 21?

I can create a table with the "lot size" but not sure how to incorporate that into the formula .

 

[JamesCanale]

I'm not sure that I follow, but if I had a divisor to deal with... As long as the total is a multiple of the divisor.

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
8	Total	Divisor	January	February	March	April	May	June	July	August	September	October	November	December
9	11991	21	987	1008	987	1008	987	1008	1008	987	1008	987	1008	1008	11991
10	500	2	40	42	42	42	42	42	40	42	42	42	42	42	500
11	9560	10	790	800	800	790	800	800	790	800	800	790	800	800	9560
12	Total		1817	1850	1829	1840	1829	1850	1838	1829	1850	1819	1850	1850
Sheet24

Cell Formulas
Range	Formula
D8:N8	D8	=EOMONTH(C8,0)+1
O9:O11	O9	=SUM(C9#)
C9:N11	C9	=LET(z,A9/B9,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)*B9
C12:N12	C12	=C9#+C10#+C11#
Dynamic array formulas.

 

[Pestomania]

I'm not sure that I follow, but if I had a divisor to deal with... As long as the total is a multiple of the divisor.

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
8	Total	Divisor	January	February	March	April	May	June	July	August	September	October	November	December
9	11991	21	987	1008	987	1008	987	1008	1008	987	1008	987	1008	1008	11991
10	500	2	40	42	42	42	42	42	40	42	42	42	42	42	500
11	9560	10	790	800	800	790	800	800	790	800	800	790	800	800	9560
12	Total		1817	1850	1829	1840	1829	1850	1838	1829	1850	1819	1850	1850
Sheet24

Cell Formulas
Range	Formula
D8:N8	D8	=EOMONTH(C8,0)+1
O9:O11	O9	=SUM(C9#)
C9:N11	C9	=LET(z,A9/B9,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)*B9
C12:N12	C12	=C9#+C10#+C11#
Dynamic array formulas.

That worked to get me the divisor! Do you know why January still doesn't want to be useD?

Total

Lot Size

1/1/2023​

2/1/2023​

3/1/2023​

4/1/2023​

5/1/2023​

6/1/2023​

7/1/2023​

8/1/2023​

9/1/2023​

10/1/2023​

11/1/2023​

12/1/2023​

Total

110​

10​

0​

10​

10​

10​

10​

10​

10​

10​

10​

10​

10​

10​

110​

80​

5​

5​

5​

10​

5​

5​

10​

5​

5​

10​

5​

5​

10​

80​

90​

30​

0​

0​

0​

30​

0​

0​

0​

30​

0​

0​

0​

30​

90​

40​

10​

0​

0​

10​

0​

0​

10​

0​

0​

10​

0​

0​

10​

40​

 

[JamesCanale]

for 110, some month doesn't get used for multiples of 10. For smaller Totals to Lot Size ratio, it's going to be a bit funny. But my formula tends to round down and correct up.

 

[Pestomania]

for 110, some month doesn't get used for multiples of 10. For smaller Totals to Lot Size ratio, it's going to be a bit funny. But my formula tends to round down and correct up.

Okay cool!! Just need to be able to explain to my boss if I get asked. Thanks so much!

 

[JamesCanale]

You can add a phase feature that you can calibrate to make things start in January -

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
14	Total	Divisor	Phase	January	February	March	April	May	June	July	August	September	October	November	########
15	110	10	1	10	10	10	10	10	10	10	10	10	10	10	0	110
16	80	5	0	5	5	10	5	5	10	5	5	10	5	5	10	80
17	90	30	3	30	0	0	0	30	0	0	0	30	0	0	0	90
18	Total			45	15	20	15	45	20	15	15	50	15	15	10
Sheet24

Cell Formulas
Range	Formula
E14:O14	E14	=EOMONTH(D14,0)+1
P15:P17	P15	=SUM(D15#)
D15:O17	D15	=INDEX(LET(z,A15/B15,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)*B15,MOD(SEQUENCE(1,12,0)+C15,12)+1)
D18:O18	D18	=D15#+D16#+D17#
Dynamic array formulas.

 

[Pestomania]

You can add a phase feature that you can calibrate to make things start in January -

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
14	Total	Divisor	Phase	January	February	March	April	May	June	July	August	September	October	November	########
15	110	10	1	10	10	10	10	10	10	10	10	10	10	10	0	110
16	80	5	0	5	5	10	5	5	10	5	5	10	5	5	10	80
17	90	30	3	30	0	0	0	30	0	0	0	30	0	0	0	90
18	Total			45	15	20	15	45	20	15	15	50	15	15	10
Sheet24

Cell Formulas
Range	Formula
E14:O14	E14	=EOMONTH(D14,0)+1
P15:P17	P15	=SUM(D15#)
D15:O17	D15	=INDEX(LET(z,A15/B15,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)*B15,MOD(SEQUENCE(1,12,0)+C15,12)+1)
D18:O18	D18	=D15#+D16#+D17#
Dynamic array formulas.

That works great too!
Do you mind me asking if you recommend any classes to learn more about this or have you learned based on necesssity? Most of what I know from formulas and vba is because of necessity.

 

[JamesCanale]

I've got about 28 years of learning what I needed to know. And the last 2 years on Mr. Excel, I've doubled my skill (or more) by solving other people's problems, and seeing how some real masters attack the same problem.

I tend to be bored with VBA because it seems like brute force and now I rarely use it. I use array formulas and accomplish things that I never would have believed I could do without VBA 2 years ago.

 

[Pestomania]

I've got about 28 years of learning what I needed to know. And the last 2 years on Mr. Excel, I've doubled my skill (or more) by solving other people's problems, and seeing how some real masters attack the same problem.

I tend to be bored with VBA because it seems like brute force and now I rarely use it. I use array formulas and accomplish things that I never would have believed I could do without VBA 2 years ago.

James,

I ended up having the opportunity to modify this formula to work for other instances with more dynamic data.

By using VLookups & Substitute, it can now process hundreds of records and types of programs. What I then did was break excel and it cannot process all of the formulas without crashing or giving #Spill records. I am working to modify, just thought it would be cool to update I am using it for more things.