thanks for the help, i will give it a test.

"joeu2004" wrote:

Errata....

Although I believe the following suggestions meets your requirements
as stated, I suspect it does not truly meet your needs. Note that the
results in B2:B6 are not in the same order as the original data. That
is, B2 does not correspond to the figure in A2 -- ergo, it does not
correspond to any descriptive text in a column parallel to A2:A6 (e.g.
category names). If you do indeed need the results to be in the same
order, I don't know if my formula is a good place to start, or if
there is a better approach altogether. Sorry, but I don't have any
more time to think about it.


On Jun 27, 4:02 pm, joeu2004 wrote:
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.