Need help automating pro-rata order allocation sheet so quantities and average prices match

[psulion01]

Here's my dilemma... Occasionally I have a handful of stock orders that are grouped together and need to be allocated on a pro-rata basis. All share allocations need to be whole numbers and each client's average price needs to be the same (to 4 decimals). If I don't force the share rounding in my sheet (using the ROUND function) then my average prices match up, but totaling the shares by hand result in missing/too many shares (since excel is calculating using decimals, i.e 15.234352 shares while my manual calculation by hand only counts 15 shares). As a result, getting the share totals to match by using ROUND will result in all average prices being mismatched.

I currently correct this through trial and error, adding and removing shares on the sheet until all totals tie out and all average prices match. What I need help with is some type of code that will be able to loop through and determine the correct whole share amounts needed to get all totals (on right and bottom) to match while also yielding an average price that matches to 4 decimal places.

Here's a sample sheet... can anyone point me in the right direction? I can make an attempt to code things, but my math skills are failing me and I'm not sure what type of calculations should be done to 'automate' my existing trial and error process to identify the correct share quantities.

Thanks!

Allocation (2).xlsx
	A	B	C	D	E	F	G	H	I
4				ClientA	ClientB	ClientC	ClientD	ClientE	Total
5			Shares	49	23	66	69	36	243
6			%ofAllocation	0.201646	0.094650	0.271605	0.283951	0.148148	100%
7	Price	Quantity
8	10	32		6	3	9	9	5	32
9	10.01	65		13	6	18	18	10	65
10	10.02	2		0	0	1	1	0	2
11	10.03	47		9	4	13	13	7	46
12	10.04	12		2	1	3	3	2	11
13	10.045	11		2	1	3	3	2	11
14	10.05	8		2	1	2	2	1	8
15	10.06	24		5	2	7	7	4	25
16	10.075	12		2	1	3	3	2	11
17	10.1	6		1	1	2	2	1	7
18	10.125	14		3	1	4	4	2	14
19	10.15	5		1	0	1	1	1	4
20	10.17	5		1	0	1	1	1	4
21
22		243	TotalShares	47	21	67	67	38	240
23		10.0402	AveragePrice	10.0407	10.0350	10.0390	10.0390	10.0418	10.0394
Sheet1

 

[snoopyhr]

Try the formatting, do not use the round function, format the shares allocated area to display integer, Cell format>custom> use this symbol # to show the integer.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1				Client*A	Client*B	Client*C	Client*D	Client*E	Total
2			Shares	49	23	66	69	36	243
3			%*of*Allocation	0.20164609	0.09465021	0.271605	0.283951	0.148148
4	Price	Quantity		Not formatted	Formatted	Formatted	Formatted	Not formatted
5	10	32		6.4526749	3	9	9	4.740741	32
6	10.01	65		13.1069959	6	18	18	9.62963	65
7	10.02	2		0.40329218		1	1	0.296296	2
8	10.03	47		9.47736626	4	13	13	6.962963	47
9	10.04	12		2.41975309	1	3	3	1.777778	12
10	10.045	11		2.218107	1	3	3	1.62963	11
11	10.05	8		1.61316872	1	2	2	1.185185	8
12	10.06	24		4.83950617	2	7	7	3.555556	24
13	10.075	12		2.41975309	1	3	3	1.777778	12
14	10.1	6		1.20987654	1	2	2	0.888889	6
15	10.125	14		2.82304527	1	4	4	2.074074	14
16	10.15	5		1.00823045		1	1	0.740741	5
17	10.17	5		1.00823045		1	1	0.740741	5
18
19		243	Total*Shares	49	23	66	69	36	243
20		10.0402	Average*Price	10.0407	10.035	10.039	10.039	10.0418	10.0394
Sheet3

 

[psulion01]

Sorry it's not that simple. I can't have partial shares allocated to clients, so the decimals need to go. I have to either round up or down to whole numbers. This is why the average prices work when i'm not rounding (because the fractional shares aren't being throw away), but don't work when I round and force the whole numbers. Follow?