My approach is as follows. But I think shg has a different approach that randomizes the off-by-one errors, which are unavoidable. I never can remember his approach.
Caveat: Because I was unable to copy-and-paste from your posted image, my percentages and prorated share amounts differ slightly from yours. However, the results are close.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH][/TH]
[TH]
A[/TH]
[TH]
B[/TH]
[TH]
C[/TH]
[TH]
D[/TH]
[/TR]
[TR]
[TD="align: center"]
1[/TD]
[TD="align: right"]
Total Shares[/TD]
[TD="align: right"]
33557[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]
2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]
3[/TD]
[TD="align: right"]
Percentage[/TD]
[TD="align: right"]
Prorated
Shares[/TD]
[TD="align: right"]
Round[/TD]
[TD="align: right"]
Cuml
Round[/TD]
[/TR]
[TR]
[TD="align: center"]
4[/TD]
[TD="align: right"]
4.95849212790250%[/TD]
[TD="align: right"]
1663.921203360240[/TD]
[TD="align: right"]
1664[/TD]
[TD="align: right"]
1664[/TD]
[/TR]
[TR]
[TD="align: center"]
5[/TD]
[TD="align: right"]
3.30567304246942%[/TD]
[TD="align: right"]
1109.284702861460[/TD]
[TD="align: right"]
1109[/TD]
[TD="align: right"]
1109[/TD]
[/TR]
[TR]
[TD="align: center"]
6[/TD]
[TD="align: right"]
6.61133625561081%[/TD]
[TD="align: right"]
2218.566107295320[/TD]
[TD="align: right"]
2219[/TD]
[TD="align: right"]
2219[/TD]
[/TR]
[TR]
[TD="align: center"]
7[/TD]
[TD="align: right"]
6.61134204650717%[/TD]
[TD="align: right"]
2218.568050546410[/TD]
[TD="align: right"]
2219[/TD]
[TD="align: right"]
2218[/TD]
[/TR]
[TR]
[TD="align: center"]
8[/TD]
[TD="align: right"]
2.47926594092860%[/TD]
[TD="align: right"]
831.967271797410[/TD]
[TD="align: right"]
832[/TD]
[TD="align: right"]
832[/TD]
[/TR]
[TR]
[TD="align: center"]
9[/TD]
[TD="align: right"]
9.91699622035434%[/TD]
[TD="align: right"]
3327.846421664310[/TD]
[TD="align: right"]
3328[/TD]
[TD="align: right"]
3328[/TD]
[/TR]
[TR]
[TD="align: center"]
10[/TD]
[TD="align: right"]
6.61346292107559%[/TD]
[TD="align: right"]
2219.279752425330[/TD]
[TD="align: right"]
2219[/TD]
[TD="align: right"]
2219[/TD]
[/TR]
[TR]
[TD="align: center"]
11[/TD]
[TD="align: right"]
1.65283419699388%[/TD]
[TD="align: right"]
554.641571485235[/TD]
[TD="align: right"]
555[/TD]
[TD="align: right"]
555[/TD]
[/TR]
[TR]
[TD="align: center"]
12[/TD]
[TD="align: right"]
1.65308763318103%[/TD]
[TD="align: right"]
554.726617066559[/TD]
[TD="align: right"]
555[/TD]
[TD="align: right"]
555[/TD]
[/TR]
[TR]
[TD="align: center"]
13[/TD]
[TD="align: right"]
1.65284313954456%[/TD]
[TD="align: right"]
554.644572336969[/TD]
[TD="align: right"]
555[/TD]
[TD="align: right"]
554[/TD]
[/TR]
[TR]
[TD="align: center"]
14[/TD]
[TD="align: right"]
4.95944478963657%[/TD]
[TD="align: right"]
1664.240888058340[/TD]
[TD="align: right"]
1664[/TD]
[TD="align: right"]
1665[/TD]
[/TR]
[TR]
[TD="align: center"]
15[/TD]
[TD="align: right"]
6.61133256465374%[/TD]
[TD="align: right"]
2218.564868720850[/TD]
[TD="align: right"]
2219[/TD]
[TD="align: right"]
2218[/TD]
[/TR]
[TR]
[TD="align: center"]
16[/TD]
[TD="align: right"]
1.65316589599464%[/TD]
[TD="align: right"]
554.752879718920[/TD]
[TD="align: right"]
555[/TD]
[TD="align: right"]
555[/TD]
[/TR]
[TR]
[TD="align: center"]
17[/TD]
[TD="align: right"]
1.32227119770626%[/TD]
[TD="align: right"]
443.714545814291[/TD]
[TD="align: right"]
444[/TD]
[TD="align: right"]
444[/TD]
[/TR]
[TR]
[TD="align: center"]
18[/TD]
[TD="align: right"]
1.98337237546647%[/TD]
[TD="align: right"]
665.560268035282[/TD]
[TD="align: right"]
666[/TD]
[TD="align: right"]
665[/TD]
[/TR]
[TR]
[TD="align: center"]
19[/TD]
[TD="align: right"]
0.82644310004856%[/TD]
[TD="align: right"]
277.329511083295[/TD]
[TD="align: right"]
277[/TD]
[TD="align: right"]
278[/TD]
[/TR]
[TR]
[TD="align: center"]
20[/TD]
[TD="align: right"]
23.13963399287930%[/TD]
[TD="align: right"]
7764.966978990490[/TD]
[TD="align: right"]
7765[/TD]
[TD="align: right"]
7765[/TD]
[/TR]
[TR]
[TD="align: center"]
21[/TD]
[TD="align: right"]
9.91700597806600%[/TD]
[TD="align: right"]
3327.849696059610[/TD]
[TD="align: right"]
3328[/TD]
[TD="align: right"]
3327[/TD]
[/TR]
[TR]
[TD="align: center"]
22[/TD]
[TD="align: right"]
4.13199658098062%[/TD]
[TD="align: right"]
1386.574092679660[/TD]
[TD="align: right"]
1387[/TD]
[TD="align: right"]
1387[/TD]
[/TR]
[TR]
[TD="align: center"]
23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]
24[/TD]
[TD="align: right"]
100.00000000000000%[/TD]
[TD="align: right"]
33557.000000000000[/TD]
[TD="align: right"]
33560[/TD]
[TD="align: right"]
33557[/TD]
[/TR]
</tbody>[/TABLE]
Code:
Formulas:
D4: =ROUND(A4*B1,0)
D5: =ROUND(SUM($A$4:A5)*$B$1 - SUM($D$4:D4), 0)
Copy D5 into D6:D22