Dividing a figure into 12 whole numbers

CROY1985

Well-known Member
Joined
Sep 21, 2009
Messages
501
Office Version
  1. 365
Platform
  1. Windows
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>
Answer to #1:


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)
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top