On Jun 27, 1:13*pm, Wizard475
wrote:
I have 5 #s. *each represents a percentage of the total. *how can i limit a
number's percentage @ 30% and have what is left move to the remaining
numbers, all the while no one number can be greatter than 30% of the total.

18 * * *0.005552647
228 * * 0.069816829
760 * * 0.232627383
1,177 * 0.360402951
1,083 * 0.331600189

3,266 *

If the first column of figures is in A2:A6 and the total is in A8, but
the following in B2 (or any parallel column, changing the references
to column B) and copy down, making sure that B1 is blank or text:

=MIN(30%*$A$8,
SUMPRODUCT((ROW(A2)=ROW($A$2:$A$6))
*LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A
$1)))
-SUM($B$1:B1))

That sums the largest N numbers, where is N is the relative row number
in column B; subtracts the sum of the previous N-1 derived values; and
limits the result to 30% of the total.

Note: An alternative expression of the SUMPRODUCT is:

SUMPRODUCT(--(ROW(A2)=ROW($A$2:$A$6)),
LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A$1)))

I remember some discussion of the relative merits; but I don't
remember what they are. In this circumstance, both forms seem to
work.