Dividing a figure into 12 whole numbers

CROY1985

Active Member
Joined
Sep 21, 2009
Messages
498
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
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:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">X</td><td style=";">Y</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">130</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Base:</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Remainder:</td><td style="text-align: right;;">-2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">Number</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Number 1</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Number 2</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Number 3</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Number 4</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Number 5</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Number 6</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Number 7</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Number 8</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Number 9</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Number 10</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Number 11</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Number 12</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=ROUNDUP(<font color="Blue">A2/B2,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=A2-B2*B4</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A8</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">$A$1:A1</font>)<=$B$2,"Number "&ROWS(<font color="Red">$A$1:A1</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=IF(<font color="Blue">A8<>"",$B$4-IF(<font color="Red">$B$2+$B$5<ROWS(<font color="Green">$A$1:A1</font>),1,0</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

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

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

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
Top