# Dividing a figure into 12 whole numbers

#### CROY1985

##### Well-known Member
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>

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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>

Excel 2003
AB
1XY
213012
3
4Base:11
5Remainder:-2
6
7Number
8Number 111
9Number 211
10Number 311
11Number 411
12Number 511
13Number 611
14Number 711
15Number 811
16Number 911
17Number 1011
18Number 1110
19Number 1210
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sheet1
Cell Formulas
RangeFormula
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)

Absolutely genius.

Thank you very much!

Replies
16
Views
832
Replies
1
Views
1K
Replies
11
Views
838
Replies
5
Views
952
Replies
3
Views
2K

1,211,710
Messages
6,103,453
Members
447,866
Latest member
bowers261

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back