"Fred Smith" 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
...
"Fred Smith" 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
...
"Fred Smith" 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
...
"Fred Smith" 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 (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
...
"Fred Smith" 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.