Rounding shares to whole numbers

[dannyt41]

Hi everyone,

This is potentially more of a math question, but I'm hoping there is an excel formula that could also help.

I'm trying to divide a number of shares by percentages with 14 decimals. The end number of shares needs to be a whole number, however whenever I round I am always left with a remainder. Example -




The example above gives a difference of 3. What is the best way to approach this type of rounding? Is there a formula I can use to ensure the rounded total is 33,557?

Many thanks,
Daniel

 

[joeu2004]

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.

	A	B	C	D
1	Total Shares	33557
2
3	Percentage	Prorated Shares	Round	Cuml Round
4	4.95849212790250%	1663.921203360240	1664	1664
5	3.30567304246942%	1109.284702861460	1109	1109
6	6.61133625561081%	2218.566107295320	2219	2219
7	6.61134204650717%	2218.568050546410	2219	2218
8	2.47926594092860%	831.967271797410	832	832
9	9.91699622035434%	3327.846421664310	3328	3328
10	6.61346292107559%	2219.279752425330	2219	2219
11	1.65283419699388%	554.641571485235	555	555
12	1.65308763318103%	554.726617066559	555	555
13	1.65284313954456%	554.644572336969	555	554
14	4.95944478963657%	1664.240888058340	1664	1665
15	6.61133256465374%	2218.564868720850	2219	2218
16	1.65316589599464%	554.752879718920	555	555
17	1.32227119770626%	443.714545814291	444	444
18	1.98337237546647%	665.560268035282	666	665
19	0.82644310004856%	277.329511083295	277	278
20	23.13963399287930%	7764.966978990490	7765	7765
21	9.91700597806600%	3327.849696059610	3328	3327
22	4.13199658098062%	1386.574092679660	1387	1387
23
24	100.00000000000000%	33557.000000000000	33560	33557

<tbody>
</tbody>

Formulas:
D4:  =ROUND(A4*B1,0)
D5:  =ROUND(SUM($A$4:A5)*$B$1 - SUM($D$4:D4), 0)

Copy D5 into D6:D22

 

[dannyt41]

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.

	A	B	C	D
1	Total Shares	33557
2
3	Percentage	Prorated Shares	Round	Cuml Round
4	4.95849212790250%	1663.921203360240	1664	1664
5	3.30567304246942%	1109.284702861460	1109	1109
6	6.61133625561081%	2218.566107295320	2219	2219
7	6.61134204650717%	2218.568050546410	2219	2218
8	2.47926594092860%	831.967271797410	832	832
9	9.91699622035434%	3327.846421664310	3328	3328
10	6.61346292107559%	2219.279752425330	2219	2219
11	1.65283419699388%	554.641571485235	555	555
12	1.65308763318103%	554.726617066559	555	555
13	1.65284313954456%	554.644572336969	555	554
14	4.95944478963657%	1664.240888058340	1664	1665
15	6.61133256465374%	2218.564868720850	2219	2218
16	1.65316589599464%	554.752879718920	555	555
17	1.32227119770626%	443.714545814291	444	444
18	1.98337237546647%	665.560268035282	666	665
19	0.82644310004856%	277.329511083295	277	278
20	23.13963399287930%	7764.966978990490	7765	7765
21	9.91700597806600%	3327.849696059610	3328	3327
22	4.13199658098062%	1386.574092679660	1387	1387
23
24	100.00000000000000%	33557.000000000000	33560	33557

<tbody>
</tbody>

Formulas:
D4:  =ROUND(A4*B1,0)
D5:  =ROUND(SUM($A$4:A5)*$B$1 - SUM($D$4:D4), 0)

Copy D5 into D6:D22

Thank you very much, the formulas work great.

Is this a fairly standard rounding approach for allocating shares? I can't find many references online to this type of calculation and would like to reference something if possible!

Many thanks,
Daniel

 

[joeu2004]

Is this a fairly standard rounding approach for allocating shares?

Ad hoc, I'm afraid.

I believe there is a "standard" algorithm or genereally accepted method. I'm sure I read a wiki page or other onine article long ago. But I'm having trouble finding it now.

My algorithm can be poorly behaved (or at least non-optimal). Consider this....

For my posted example, several rows round in the opposite direction: row 7, 13, 15, 18 and 21 round down; and row 14 and 19 round up. The sum of squared errors (SSE) is about 4.03.

In contrast, the SSE is lower, about 2.17, if we force only row 6, 15 and 18 to round down, the opposite direction.

 

[shg]

Another way:

	A​	B​	C​
1​	Total Shares​	33557​	​
2​
3​	Pct​	Shares​
4​	4.95849212790250%​	1664​	B4: =ROUND(A4 / (A$24 - SUM(A$3:A3)) * (B$1 - SUM(B$3:B3)), 0)
5​	3.30567304246942%​	1109​
6​	6.61133625561081%​	2219​
7​	6.61134204650717%​	2219​
8​	2.47926594092860%​	832​
9​	9.91699622035434%​	3328​
10​	6.61346292107559%​	2219​
11​	1.65283419699388%​	555​
12​	1.65308763318103%​	555​
13​	1.65284313954456%​	555​
14​	4.95944478963657%​	1664​
15​	6.61133256465374%​	2218​
16​	1.65316589599464%​	555​
17​	1.32227119770626%​	444​
18​	1.98337237546647%​	665​
19​	0.82644310004856%​	277​
20​	23.13963399287930%​	7765​
21​	9.91700597806600%​	3328​
22​	4.13199658098062%​	1386​
23​
24​	100.00000000000000%​	33557​

 

[dannyt41]

Thank you both for your replies and formulas. Much appreciated.

Best,
Daniel