Dividing a figure into 12 whole numbers

[CROY1985]

Hi all,

I am in need of some help, I need a formula which will divide X into Y whole numbers, the sum of which is X - so I can’t simply use round as it wont necessarily come back to X. Here is an example, let’s call X 130, and Y 12. </SPAN>
The profile I would want is: </SPAN>
11,11,11,11,11,11,11,11,11,11,10,10
</SPAN>
Basically I want the remainder shared out over the months rather than appearing at the end. </SPAN>
It would be preferable if the higher and lower numbers could be randomised, like this: </SPAN>
11,11,10,11,11,11,11,11,10,11,11,11,</SPAN>

If this is too tricky, then the first profile would be acceptable. </SPAN>
Hope you can be of assistance.
</SPAN>
Thanks</SPAN>

 

[Sal Paradise]

Hi all,

I am in need of some help, I need a formula which will divide X into Y whole numbers, the sum of which is X - so I can’t simply use round as it wont necessarily come back to X. Here is an example, let’s call X 130, and Y 12. </SPAN>
The profile I would want is: </SPAN>
11,11,11,11,11,11,11,11,11,11,10,10
</SPAN>
Basically I want the remainder shared out over the months rather than appearing at the end. </SPAN>
It would be preferable if the higher and lower numbers could be randomised, like this: </SPAN>
11,11,10,11,11,11,11,11,10,11,11,11,</SPAN>

If this is too tricky, then the first profile would be acceptable. </SPAN>
Hope you can be of assistance.
</SPAN>
Thanks</SPAN>

Answer to #1:

Excel 2003
	A	B
1	X	Y
2	130	12
3
4	Base:	11
5	Remainder:	-2
6
7		Number
8	Number 1	11
9	Number 2	11
10	Number 3	11
11	Number 4	11
12	Number 5	11
13	Number 6	11
14	Number 7	11
15	Number 8	11
16	Number 9	11
17	Number 10	11
18	Number 11	10
19	Number 12	10
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sheet1

Cell Formulas
Range	Formula
B4	=ROUNDUP(A2/B2,0)
B5	=A2-B2*B4
B8	=IF(A8<>"",$B$4-IF($B$2+$B$5$A$1:A1),1,0),"")
A8	=IF(ROWS($A$1:A1)<=$B$2,"Number "&ROWS($A$1:A1),"")

Copy down as needed (I don't know how big your Y gets)

 

[CROY1985]

Absolutely genius.

Thank you very much!