"Ron Rosenfeld" 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" wrote in message
...
On Sun, 17 Jan 2010 16:43:38 -0700, "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?

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