Allocating percentages
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.
|