I have percentages in B2:B30 which add up to 100%, and an amount to allocate 
in A2 (for example, 8). However, the results must be integers, and must add 
up to A2. The simple formula is =round($A$2*B2,0) copied down, but the 
rounding doesn't always produce the right total. How do I ensure that the 
total always equals A2?

Thanks,
Fred 

"Fred Smith" <fsmith11@yahooo.com> wrote:
> I have percentages in B2:B30 which add up to 100%,
> and an amount to allocate in A2 (for example, 8).
> However, the results must be integers, and must add up to A2. The simple 
> formula is =round($A$2*B2,0)
> copied down, but the rounding doesn't always produce
> the right total. How do I ensure that the total always
> equals A2?

I believe I already answered that in Lilyput's thread.  I'll paraphrase....

This is a common quantization problem, i.e. the result of rounding "long" 
decimal fractions to fewer decimal places (or integers).  There are no 
perfect solutions.

Consider the following simple example.  You have 3 dollar bills, and you 
want to award them to 4 people in the proportion to their contributions, 
which is 25% each.  It can't be done!  At least, not fairly.  Someone must 
get zero.

One simple (but flawed) approach is to put the following formulas into C2 
and C3, say, and copy C3 down through C30:

C2:  =ROUND($A$2*B2,0)

C3:  =MIN($A$2 - SUM($C$2:C2), ROUND($A$2*B3,0))

That approach is flawed because it is more unfair to the people represented 
by the later cells.

We might ameliorate the unfairness by randomizing B2:B30 (and associated 
columns), using the formula above, then reordering C2:C30 according to the 
original order.  I would use a UDF for that. 

PS....
> One simple (but flawed) approach is [....]

I seem to recall posting (or at least preparing for posting or email) a 
solution to another similar problem that ameliorated the unfairness by 
ensuring that everyone gets at least one (if they deserve it), then everyone 
gets at least two (if they deserve it), etc per the OP's request.

Some people might consider that to be more fair, even if it means that a 50% 
contributor might get the same as everyone else due to quantization.

I don't remember if my approach worked.  But if that's something that 
Lilyput would like to consider, I can search for it.

(It's a difficult search for me.  So I don't want to start doing it unless 
there is some interest.)


----- original message -----

"Joe User" <joeu2004> wrote in message 
news:%232xgqF9lKHA.3840@TK2MSFTNGP06.phx.gbl...
> "Fred Smith" <fsmith11@yahooo.com> wrote:
>> I have percentages in B2:B30 which add up to 100%,
>> and an amount to allocate in A2 (for example, 8).
>> However, the results must be integers, and must add up to A2.
>> The simple formula is =round($A$2*B2,0)
>> copied down, but the rounding doesn't always produce
>> the right total. How do I ensure that the total always
>> equals A2?
>
> I believe I already answered that in Lilyput's thread.  I'll 
> paraphrase....
>
> This is a common quantization problem, i.e. the result of rounding "long" 
> decimal fractions to fewer decimal places (or integers).  There are no 
> perfect solutions.
>
> Consider the following simple example.  You have 3 dollar bills, and you 
> want to award them to 4 people in the proportion to their contributions, 
> which is 25% each.  It can't be done!  At least, not fairly.  Someone must 
> get zero.
>
> One simple (but flawed) approach is to put the following formulas into C2 
> and C3, say, and copy C3 down through C30:
>
> C2:  =ROUND($A$2*B2,0)
>
> C3:  =MIN($A$2 - SUM($C$2:C2), ROUND($A$2*B3,0))
>
> That approach is flawed because it is more unfair to the people 
> represented by the later cells.
>
> We might ameliorate the unfairness by randomizing B2:B30 (and associated 
> columns), using the formula above, then reordering C2:C30 according to the 
> original order.  I would use a UDF for that. 

Errata....

I wrote:
> We might ameliorate the unfairness by randomizing
> B2:B30 (and associated columns), using the formula
> above, then reordering C2:C30 according to the original order.  I would 
> use a UDF for that.

I suspect most people (those represented by B2:B30) would not consider that 
to be less unfair.

It would probably be more fair to effectively sort B2:B30 in descending 
order, then apply the distribution.  Again, you could reorder C2:C30 
according to the original order, if need be; and in that case, I would use a 
UDF.


----- original message ----- 

"Joe User" <joeu2004> wrote in message 
news:%232xgqF9lKHA.3840@TK2MSFTNGP06.phx.gbl...
> "Fred Smith" <fsmith11@yahooo.com> wrote:
>> I have percentages in B2:B30 which add up to 100%,
>> and an amount to allocate in A2 (for example, 8).
>> However, the results must be integers, and must add up to A2. The simple 
>> formula is =round($A$2*B2,0)
>> copied down, but the rounding doesn't always produce
>> the right total. How do I ensure that the total always
>> equals A2?
>
> I believe I already answered that in Lilyput's thread.  I'll 
> paraphrase....
>
> This is a common quantization problem, i.e. the result of rounding "long" 
> decimal fractions to fewer decimal places (or integers).  There are no 
> perfect solutions.
>
> Consider the following simple example.  You have 3 dollar bills, and you 
> want to award them to 4 people in the proportion to their contributions, 
> which is 25% each.  It can't be done!  At least, not fairly.  Someone must 
> get zero.
>
> One simple (but flawed) approach is to put the following formulas into C2 
> and C3, say, and copy C3 down through C30:
>
> C2:  =ROUND($A$2*B2,0)
>
> C3:  =MIN($A$2 - SUM($C$2:C2), ROUND($A$2*B3,0))
>
> That approach is flawed because it is more unfair to the people 
> represented by the later cells.
>
> We might ameliorate the unfairness by randomizing B2:B30 (and associated 
> columns), using the formula above, then reordering C2:C30 according to the 
> original order.  I would use a UDF for that. 

Errata (again)....

I wrote:
> One simple (but flawed) approach is to put the following
> formulas into C2 and C3, say, and copy C3 down through C30:
> C2:  =ROUND($A$2*B2,0)
> C3:  =MIN($A$2 - SUM($C$2:C2), ROUND($A$2*B3,0))

I think C30 should have the formula:

C30:  =$A$2 - SUM($C$2:C29)

That may be necessary to remedy the case where SUM(C2:C29)+ROUND(A2*B3,0) is 
less than A2.

At least, I __think__ that is a possibility.  In any case, the 
non-generalization cannot hurt, even it proves to be surperfluous.


----- original message -----

"Joe User" <joeu2004> wrote in message 
news:%232xgqF9lKHA.3840@TK2MSFTNGP06.phx.gbl...
> "Fred Smith" <fsmith11@yahooo.com> wrote:
>> I have percentages in B2:B30 which add up to 100%,
>> and an amount to allocate in A2 (for example, 8).
>> However, the results must be integers, and must add up to A2. The simple 
>> formula is =round($A$2*B2,0)
>> copied down, but the rounding doesn't always produce
>> the right total. How do I ensure that the total always
>> equals A2?
>
> I believe I already answered that in Lilyput's thread.  I'll 
> paraphrase....
>
> This is a common quantization problem, i.e. the result of rounding "long" 
> decimal fractions to fewer decimal places (or integers).  There are no 
> perfect solutions.
>
> Consider the following simple example.  You have 3 dollar bills, and you 
> want to award them to 4 people in the proportion to their contributions, 
> which is 25% each.  It can't be done!  At least, not fairly.  Someone must 
> get zero.
>
> One simple (but flawed) approach is to put the following formulas into C2 
> and C3, say, and copy C3 down through C30:
>
> C2:  =ROUND($A$2*B2,0)
>
> C3:  =MIN($A$2 - SUM($C$2:C2), ROUND($A$2*B3,0))
>
> That approach is flawed because it is more unfair to the people 
> represented by the later cells.
>
> We might ameliorate the unfairness by randomizing B2:B30 (and associated 
> columns), using the formula above, then reordering C2:C30 according to the 
> original order.  I would use a UDF for that. 

On Sun, 17 Jan 2010 16:43:38 -0700, "Fred Smith" <fsmith11@yahooo.com> wrote:

>I have percentages in B2:B30 which add up to 100%, and an amount to allocate 
>in A2 (for example, 8). However, the results must be integers, and must add 
>up to A2. The simple formula is =round($A$2*B2,0) copied down, but the 
>rounding doesn't always produce the right total. How do I ensure that the 
>total always equals A2?
>
>Thanks,
>Fred 

I have run into a similar situation recently, trying to completely distribute a
pot of money according to a predetermined percentage distribution.

I would use the formula you have in B2:B29

However, for B30, I would use the formula =a2-sum(b2:b29)

I believe, although I don't know how to prove it mathematically, that this
should result in no one being off by more than one unit, from what they might
receive if the units were smaller.

I would also use what is euphemistically termed bankers rounding, where the
rounding is done towards the nearest even number for a value that is half
between.  That produces a less biased result than the round half up method,
which always rounds up when at the halfway point.

The VBA Round function uses "banker's rounding".  The Excel worksheet function
uses algebraic rounding.

For my purposes, the round to even method gives a result that is suitable. But,
depending on the distribution of your numbers, other methods may be appropriate
for you, and the round half up may be OK, even with the bias it introduces.



--ron

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote:
> I would also use what is euphemistically termed bankers rounding

It is not likely to make much of any different.  VBA Round(x,0) and Excel 
ROUND(x,0) differ only when MOD(x,1) is exactly 0.5 (and INT(X) is even).

(Techically, when MOD(x,1) is exactly 0.5 within 15 significant digits for 
Excel ROUND, notwithstanding defects.)


> I would use the formula you have in B2:B29
> However, for B30, I would use the formula =a2-sum(b2:b29)

Consider A2=15, B2:B11=10%, and B12:B30=0%.  Put =ROUND($A$2*B2,0) into C2, 
copy down through C29, and put =$A$2-SUM(B2:B29) into C30.

C2:C11=2, which sums to 20(!).  C12:C29=0, but C30=-5(!).  True, the sum is 
15; but some of the line item values are incorrect.

(And that is true whether you use ROUND or VBA Round.)

Now, try what I suggested initially.  Put 
=MIN($A$2-SUM($C$2:C2),ROUND($A$2*B3,0)) into C3 and copy down through C30.

C2:C8=2 and C9=1, which sums to 15.  C10:C30=0.

However, as I noted subsequently, I believe there are cases when 
=A2-SUM(B2:B29) in C30 also needed.  "The exercise is left to the student" 
:-).


----- original message ----- 

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:t2e7l514u1johupbvq6uqnj05s3sjqkic4@4ax.com...
> On Sun, 17 Jan 2010 16:43:38 -0700, "Fred Smith" <fsmith11@yahooo.com> 
> wrote:
>
>>I have percentages in B2:B30 which add up to 100%, and an amount to 
>>allocate
>>in A2 (for example, 8). However, the results must be integers, and must 
>>add
>>up to A2. The simple formula is =round($A$2*B2,0) copied down, but the
>>rounding doesn't always produce the right total. How do I ensure that the
>>total always equals A2?
>>
>>Thanks,
>>Fred
>
> I have run into a similar situation recently, trying to completely 
> distribute a
> pot of money according to a predetermined percentage distribution.
>
> I would use the formula you have in B2:B29
>
> However, for B30, I would use the formula =a2-sum(b2:b29)
>
> I believe, although I don't know how to prove it mathematically, that this
> should result in no one being off by more than one unit, from what they 
> might
> receive if the units were smaller.
>
> I would also use what is euphemistically termed bankers rounding, where 
> the
> rounding is done towards the nearest even number for a value that is half
> between.  That produces a less biased result than the round half up 
> method,
> which always rounds up when at the halfway point.
>
> The VBA Round function uses "banker's rounding".  The Excel worksheet 
> function
> uses algebraic rounding.
>
> For my purposes, the round to even method gives a result that is suitable. 
> But,
> depending on the distribution of your numbers, other methods may be 
> appropriate
> for you, and the round half up may be OK, even with the bias it 
> introduces.
>
>
>
> --ron 

Errata (yet again)....

Sorry for the incessant responses.

I wrote:
> One simple (but flawed) approach is to put the following formulas into C2 
> and C3, say, and copy C3 down through C30: C2:  =ROUND($A$2*B2,0)
> C3:  =MIN($A$2 - SUM($C$2:C2), ROUND($A$2*B3,0))

I think it would be better if C3 is:

=MIN($A$2,ROUND($A$2*SUM($B$2:B2),0)) - SUM($C$2:C2)

especially when B2:B30 is sorted in descending order.

And then I believe it is unnecessary to make a special case of C30 (my 
previous suggestion of $A$2-SUM($C$2:C29)).

However, that is still flawed in some respects.  Consider Lilyput's example 
(below).

(By the way, did you notice that her percentages do not add up to 100%?  I 
assume there is a typo.  So I changed her 2nd-to-last entry from 2.00% to 
2.40%.)

When B2:B30 is not sorted, the non-zero entries would be:

27.90%  2
1.50%   1
1.40%   1
1.60%   1
10.50%  1
9.50%   1
2.90%   1

But it might seem counter-intuitive that people with 1.4%, 1.5% and 1.6% 
each get 1, but people with 3.0%, 3.6%, 3.7% and 5.0% get 0.

Sorting B2:B30 in descending order has slightly better results, to wit:

27.90%  2
10.50%  1
9.50%   1
3.70%   1
2.80%   1
2.00%   1
1.40%   1

But there are still inexplicable anomalies; I mean anomalies for which 
people will not understand the explanation.


-----
Lilyput's data:

A2:
8

B2:B30:
1.50%
2.30%
1.80%
1.00%
5.40%
1.40%
2.10%
1.40%
3.60%
0.90%
1.70%
2.00%
1.60%
1.90%
10.50%
3.70%
1.10%
1.40%
2.10%
9.50%
2.80%
0.80%
2.90%
0.60%
2.40%
2.70%


----- original message -----

"Joe User" <joeu2004> wrote in message 
news:%232xgqF9lKHA.3840@TK2MSFTNGP06.phx.gbl...
> "Fred Smith" <fsmith11@yahooo.com> wrote:
>> I have percentages in B2:B30 which add up to 100%,
>> and an amount to allocate in A2 (for example, 8).
>> However, the results must be integers, and must add up to A2. The simple 
>> formula is =round($A$2*B2,0)
>> copied down, but the rounding doesn't always produce
>> the right total. How do I ensure that the total always
>> equals A2?
>
> I believe I already answered that in Lilyput's thread.  I'll 
> paraphrase....
>
> This is a common quantization problem, i.e. the result of rounding "long" 
> decimal fractions to fewer decimal places (or integers).  There are no 
> perfect solutions.
>
> Consider the following simple example.  You have 3 dollar bills, and you 
> want to award them to 4 people in the proportion to their contributions, 
> which is 25% each.  It can't be done!  At least, not fairly.  Someone must 
> get zero.
>
> One simple (but flawed) approach is to put the following formulas into C2 
> and C3, say, and copy C3 down through C30:
>
> C2:  =ROUND($A$2*B2,0)
>
> C3:  =MIN($A$2 - SUM($C$2:C2), ROUND($A$2*B3,0))
>
> That approach is flawed because it is more unfair to the people 
> represented by the later cells.
>
> We might ameliorate the unfairness by randomizing B2:B30 (and associated 
> columns), using the formula above, then reordering C2:C30 according to the 
> original order.  I would use a UDF for that. 

On Sun, 17 Jan 2010 22:49:23 -0800, "Joe User" <joeu2004> wrote:

>"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote:
>> I would also use what is euphemistically termed bankers rounding
>
>It is not likely to make much of any different.  VBA Round(x,0) and Excel 
>ROUND(x,0) differ only when MOD(x,1) is exactly 0.5 (and INT(X) is even).
>
>(Techically, when MOD(x,1) is exactly 0.5 within 15 significant digits for 
>Excel ROUND, notwithstanding defects.)
>
>
>> I would use the formula you have in B2:B29
>> However, for B30, I would use the formula =a2-sum(b2:b29)
>
>Consider A2=15, B2:B11=10%, and B12:B30=0%.  Put =ROUND($A$2*B2,0) into C2, 
>copy down through C29, and put =$A$2-SUM(B2:B29) into C30.
>
>C2:C11=2, which sums to 20(!).  C12:C29=0, but C30=-5(!).  True, the sum is 
>15; but some of the line item values are incorrect.
>
>(And that is true whether you use ROUND or VBA Round.)
>
>Now, try what I suggested initially.  Put 
>=MIN($A$2-SUM($C$2:C2),ROUND($A$2*B3,0)) into C3 and copy down through C30.
>
>C2:C8=2 and C9=1, which sums to 15.  C10:C30=0.
>
>However, as I noted subsequently, I believe there are cases when 
>=A2-SUM(B2:B29) in C30 also needed.  "The exercise is left to the student" 
>:-).

I think the kind of rounding that needs to be used will depend on the data and
the expected results.  As you wrote, there is no single perfect method.

I was not clear last night, but I think the goal of the rounding method used
should attempt to result in no distribution being off by more than one unit.

For dividing pots of money, it may usually be the case that round half to even,
or round half up, is good enough.  For other types of data and divisions, it
may be better to use stochastic rounding, where halves are randomly rounded up
or down; or even dithering.
--ron

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote:
> For dividing pots of money, it may usually be the case
> that round half to even, or round half up, is good enough.

You fail to acknowledge and seem to fail to notice that I demonstrated by 
example that it is not.

Moreover, when rounding dollars-and-cents to dollars, the rounding algorithm 
("banker's" or normal) makes a difference only when the amount ends in 
exactly 50 cents.  Yes, that does depend on the data.  But in general, that 
is unusual.


----- original message -----

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:ebl8l5l6asgm2tfibbs9bpreauq0nto3n7@4ax.com...
> On Sun, 17 Jan 2010 22:49:23 -0800, "Joe User" <joeu2004> wrote:
>
>>"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote:
>>> I would also use what is euphemistically termed bankers rounding
>>
>>It is not likely to make much of any different.  VBA Round(x,0) and Excel
>>ROUND(x,0) differ only when MOD(x,1) is exactly 0.5 (and INT(X) is even).
>>
>>(Techically, when MOD(x,1) is exactly 0.5 within 15 significant digits for
>>Excel ROUND, notwithstanding defects.)
>>
>>
>>> I would use the formula you have in B2:B29
>>> However, for B30, I would use the formula =a2-sum(b2:b29)
>>
>>Consider A2=15, B2:B11=10%, and B12:B30=0%.  Put =ROUND($A$2*B2,0) into 
>>C2,
>>copy down through C29, and put =$A$2-SUM(B2:B29) into C30.
>>
>>C2:C11=2, which sums to 20(!).  C12:C29=0, but C30=-5(!).  True, the sum 
>>is
>>15; but some of the line item values are incorrect.
>>
>>(And that is true whether you use ROUND or VBA Round.)
>>
>>Now, try what I suggested initially.  Put
>>=MIN($A$2-SUM($C$2:C2),ROUND($A$2*B3,0)) into C3 and copy down through 
>>C30.
>>
>>C2:C8=2 and C9=1, which sums to 15.  C10:C30=0.
>>
>>However, as I noted subsequently, I believe there are cases when
>>=A2-SUM(B2:B29) in C30 also needed.  "The exercise is left to the student"
>>:-).
>
> I think the kind of rounding that needs to be used will depend on the data 
> and
> the expected results.  As you wrote, there is no single perfect method.
>
> I was not clear last night, but I think the goal of the rounding method 
> used
> should attempt to result in no distribution being off by more than one 
> unit.
>
> For dividing pots of money, it may usually be the case that round half to 
> even,
> or round half up, is good enough.  For other types of data and divisions, 
> it
> may be better to use stochastic rounding, where halves are randomly 
> rounded up
> or down; or even dithering.
> --ron 

On Mon, 18 Jan 2010 11:01:40 -0800, "Joe User" <joeu2004> wrote:

>You fail to acknowledge and seem to fail to notice that I demonstrated by 
>example that it is not.

I quoted your comment about there being no perfect method.  But if it makes you
feel better, I explicitly acknowledge than one can construct sets of data and
rules for division for which the simpler rounding algorithms are inadequate,
and the data set and rules you posted earlier are an example of that
inadequacy.

Your example, although demonstrating this point is, in my opinion, unrealistic.

I think it more likely that those who are dividing a pot of $15 with a rule of
10% to 10 people, and 0% to 10 people, rounded to the nearest $1, would come up
with a different rule.

The situation I am dealing with has to do with dividing a much larger pot
amongst many fewer people, and the maximum deviation from perfect has been a
mere penny, using simple rounding algorithms.
--ron

On Mon, 18 Jan 2010 14:30:50 -0500, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>The situation I am dealing with has to do with dividing a much larger pot
>amongst many fewer people, and the maximum deviation from perfect has been a
>mere penny, using simple rounding algorithms.

And, of course, the deviation could be greater depending on the various
parameters that make up the rules; in which case, a more involved algorithm
would be required; than merely rounding the first n computations and taking the
difference for the last.
--ron