"Ron Rosenfeld" wrote:
In general, you would compute n-1 of the numbers, then
subtract them from the total to get the nth number.
So if your formulas are in I6:I8, they might look like:
$I$6: =ROUND($H$35*H6,0)
$I$7: =ROUND($H$35*H7,0)
$I$8: =$H$35-SUM(I6:I7)

Although that does work for the example given, and it might work for any
__3__ percentages, it does not work "in general" for any n percentages.

Consider an example with 15% in H6:H11 and 10% in H12. With a total of 17
in H35, the result in I6:I12 would be 3,3,3,3,3,3 (a subtotal of 18!)
and -1(!).

A more reliable, but still simple approach is:

I6: =ROUND(H6*$H$35,0)
I7: =ROUND(SUM($H$6:H7)*$H$35-SUM($I$6:I6),0)

Copy I7 down through I12.

The ROUND function in I12 should mask any arithmetic anomalies that arise
because SUM(H6:H12) might not be (usually is not) exactly 100%. But if you
prefer, the formula in I12 could be:

I12: =$H$35-SUM($I$6:I11)

The result in I6:I12 is 3,2,3,2,3,2,2, which does indeed sum to 17.

For TreeHugger's original example, the result is 3,6,8, which is the same as
Ron's approach.

-----

For an algorithm that claims to minimize absolute relative error, you might
look at Bernd Plumhoff's webpage at
http://www.sulprobil.com/html/largest_remainder.html.

But frankly, I usually find his descriptions confusing. And I have not been
able to download his Excel implementations for a long time. (Is a donation
__required__ now?) Moreover, many (all?) of the files now require Excel
2007 or later.

PS: And I coulda sworn his we