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.