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.

 

[JamesCanale]

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
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
3	110	9	9	9	9	9	10	9	9	9	9	9	10
4	51	4	4	4	5	4	4	4	5	4	4	4	5
5	Total	21	21	22	22	21	23	21	22	22	21	21	24
Sheet24

Cell Formulas
Range	Formula
C1:M1	C1	=EOMONTH(B1,0)+1
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),IF(e=0,d+1,d))
B5:M5	B5	=B2#+B3#+B4#
Dynamic array formulas.

 

[Pestomania]

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
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
3	110	9	9	9	9	9	10	9	9	9	9	9	10
4	51	4	4	4	5	4	4	4	5	4	4	4	5
5	Total	21	21	22	22	21	23	21	22	22	21	21	24
Sheet24

Cell Formulas
Range	Formula
C1:M1	C1	=EOMONTH(B1,0)+1
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),IF(e=0,d+1,d))
B5:M5	B5	=B2#+B3#+B4#
Dynamic array formulas.

That is probably the coolest formula I have EVER used!!! Thank you!

 

[JamesCanale]

I like to wait to see what some of the cleverer people post as solutions. The most interesting ones are the shortest.

 

[Pestomania]

I like to wait to see what some of the cleverer people post as solutions. The most interesting ones are the shortest.

So it populated a problem, I have highlihtted.

Total

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

100​

8​

8​

9​

8​

8​

9​

8​

8​

9​

8​

8​

9​

100​

12​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

24​

5​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

1​

1​

28​

2​

2​

3​

2​

2​

3​

2​

2​

3​

2​

2​

3​

28​

12​

12​

14​

12​

12​

14​

12​

12​

14​

12​

12​

15​

The red fields are just sum of January to December and it is not returning the correct number

 

[Georgiboy]

A fairly simple solution:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Annual Total	January	February	March	April	May	June	July	August	September	October	November	December
2	110	11	9	9	9	9	9	9	9	9	9	9	9
3	50	6	4	4	4	4	4	4	4	4	4	4	4
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=A2-SUM(C2:M2)
C2:M3	C2	=INT($A2/12)

 

[Pestomania]

A fairly simple solution:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Annual Total	January	February	March	April	May	June	July	August	September	October	November	December
2	110	11	9	9	9	9	9	9	9	9	9	9	9
3	50	6	4	4	4	4	4	4	4	4	4	4	4
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=A2-SUM(C2:M2)
C2:M3	C2	=INT($A2/12)

Hi,

This works great but it front loads the year heavily during different numbers.

If I use 80, this is the distribution:

80​

14​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

Is there a way to make that more even?

 

[JamesCanale]

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.

 

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

I must have screwed something up when I pasted it, now it works again. Apologies!

 

[JamesCanale]

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.