Formula to distribute units based on weight, rounded to whole number, but sum to proper total

[amyvdk]

Hi there,
I'm having trouble figuring out a formula that will distribute my total number of units by weight. I need to round the result to the nearest whole number but I also need the total units once distributed to sum to the proper total.

[TABLE="width: 1023"]
<tbody>[TR]
[TD]Row 1[/TD]
[TD]6.2%[/TD]
[TD]6.4%[/TD]
[TD]8.6%[/TD]
[TD]9.4%[/TD]
[TD]10.4%[/TD]
[TD]9.6%[/TD]
[TD]8.1%[/TD]
[TD]7.7%[/TD]
[TD]7.8%[/TD]
[TD]9.1%[/TD]
[TD]9.0%[/TD]
[TD]7.7%[/TD]
[TD]100.0%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]JANUARY[/TD]
[TD]FEBRUARY[/TD]
[TD]MARCH[/TD]
[TD]APRIL[/TD]
[TD]MAY[/TD]
[TD]JUNE[/TD]
[TD]JULY[/TD]
[TD]AUGUST[/TD]
[TD]SEPTEMBER[/TD]
[TD]OCTOBER[/TD]
[TD]NOVEMBER[/TD]
[TD]DECEMBER[/TD]
[TD]TOTAL[/TD]
[TD]Actual total[/TD]
[/TR]
[TR]
[TD="align: right"]Row 4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]formula in row 4:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]IF(ISERROR($M$4*A1)," ",($M$4*A1))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

Based on what I understood, are you looking for something like below:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	6.20%	6.40%	8.60%	9.40%	10.40%	9.60%	8.10%	7.70%	7.80%	9.10%	9.00%	7.70%	100.00%
2
3	JANUARY	FEBRUARY	MARCH	APRIL	MAY	JUNE	JULY	AUGUST	SEPTEMBER	OCTOBER	NOVEMBER	DECEMBER	TOTAL	Actual total
4	1	1	1	1	2	1	1	1	1	1	1	1	15	13
5	1	1	1	1	1	1	1	1	1	1	1	1	9	12
Sheet1

Cell Formulas
Range	Formula
A4	=IF($M4="","",ROUND($M4*A$1,0))
N4	=SUM(A4:L4)

 

[amyvdk]

Based on what I understood, are you looking for something like below:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
JANUARY	FEBRUARY	MARCH	APRIL	MAY	JUNE	JULY	AUGUST	SEPTEMBER	OCTOBER	NOVEMBER	DECEMBER	TOTAL	Actual total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]6.20%[/TD]
[TD="align: right"]6.40%[/TD]
[TD="align: right"]8.60%[/TD]
[TD="align: right"]9.40%[/TD]
[TD="align: right"]10.40%[/TD]
[TD="align: right"]9.60%[/TD]
[TD="align: right"]8.10%[/TD]
[TD="align: right"]7.70%[/TD]
[TD="align: right"]7.80%[/TD]
[TD="align: right"]9.10%[/TD]
[TD="align: right"]9.00%[/TD]
[TD="align: right"]7.70%[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]=IF($M4="","",ROUND($M4*A$1,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N4[/TH]
[TD="align: left"]=SUM(A4:L4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

That's certainly an option, it's the inverse way of thinking about it. The original goal was to distribute the 15 units across each month by seasonal weight.