Formula to divide proportionally?

[Stride1978]

I have a range of 6 cells that I am dividing days between based on their %. My problem is if there are only 3 of those 6 cells not blank they are all 3, 33%. It can not add up to more than the total which is 100. Likewise if only 2 cells and obviously each are 50% and I need 50% of 25 one would have to be 12 and the other 13. Below is how it needs to be but can't make it happen.

Col A. Col B
33%. 34
33%. 33
33%. 33

Has to equal whole numbers. Can't have decimals. I've tried the round and several others but can't round 33.33 bc they all round to 33.

Basically I need a formula to divide equally a number of days by up to 6 cells.

Any help would be greatly appreciated. I apologize in advance if I have left out any info you need to help me. Thank you!

 

[AgentSmith]

Perhaps an easier approach is to work cumulatively. So if you have a total of 10 days it looks like this:

Col A Col B Col C Col D
34% 34% 3 3
33% 67% 7 4
33% 100% 10 3

Formulae
B1: =A1
B2: =B1+A2 [copy down]
C1: =ROUND($F$1*B1,0) [copy down]
D1: =C1
D2: =C2-C1 [copy down]

Should do the trick.

 

[Stride1978]

The only thing is the % assigned is based on a #/36 to get the %. So for example a maximum of 36 months to be divided (30 day months). But there may only be 25 months and 25 days available to divide. The rule is to take the attempted amount (ie. 12 months to 3 different cells) divided by 36 to get the %. Each cell gets that proportion of the 25 months and 25 days available.

Sometimes it could be 18 months to one cell and 12 to the other. Sorry, hope that made sense. I can get it all to work it's just when the %'s are all exactly the same that I can't bc the total doesn't equal the 25 months and 25 days.

 

[Sal Paradise]

<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 /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Percents</td><td style=";">Values</td><td style="text-align: right;;"></td><td style=";">Total</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">20%</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></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="text-align: right;;">40%</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></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="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></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">B2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">A2:A$7,">0"</font>)=1,$E$1-SUM(<font color="Red">B$1:B1</font>),A2*$E$1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Stride1978]

I can't upload it right now but to make the question simpler, here's what I need.

The total number is 25 days.

col A. Col B

50% a1*25 returns 12.5
50% a2*25 returns 12.5

Which does equal the total of 25 but I need whole numbers only so I need 1 to round to 13 and the other to 12.

 

[AgentSmith]

I don't understand what the problem is with my first reply - sorry. Might be easier to explain if you mock it up and post using HTML maker.

 

[shg]

I think this is essentially the same as AgentSmith's solution:

<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=98><COLGROUP><COL style="WIDTH: 37pt" span=2 width=49><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 height=16 width=49></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=49>Target</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 height=16></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right>100</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16 width=49>Pct</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=49>Amt</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=16 align=right>37.3%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right>37</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=16 align=right>12.7%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right>13</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=16 align=right>22.1%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right>22</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=16 align=right>27.9%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right>28</TD></TR></TBODY></TABLE>

The formula in B4 and down is

=IF(A4=0, 0, ROUND(A4 * (B$2 - SUM(B$3:B3) ) / (1 - SUM(A$3:A3)), 0))

 

[Stride1978]

Perhaps an easier approach is to work cumulatively. So if you have a total of 10 days it looks like this:

Col A Col B Col C Col D
34% 34% 3 3
33% 67% 7 4
33% 100% 10 3

Formulae
B1: =A1
B2: =B1+A2 [copy down]
C1: =ROUND($F$1*B1,0) [copy down]
D1: =C1
D2: =C2-C1 [copy down]

Should do the trick.

Agent Smith please accept my apologies that worked perfectly! I must have entered something incorrectly yesterday. Thank you some much!