Limited value based on percentage of total

[Wizard475]

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

 

[Glenn]

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

With your numbers in A1:A5, sorted descending

B1=IF(A1>SUM($A$1:$A$5)*0.3,INT(SUM($A$1:$A$5)*0.3),A1)
B2=IF((A2+SUM($A$1:A1)-SUM($B$1:B1))>SUM($A$1:$A$5)*0.3,INT(SUM($A$1:$A$5)*0.3),(A2+SUM($A$1:A1)-SUM($B$1:B1)))

Fill B2 down to B5

If that's not the result you desire, then more information is needed.

 

[Wizard475]

thanks for the response. what if i am unable to sort the values?

 

[joeu2004]

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.

 

[joeu2004]

PS...

*LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A$1))

Sorry about the typos and text alignment in the previous posting. I
hope it is clear.

I just wanted to add.... Since ROW($A$1) always returns 1, you might
wonder: why not simply write 1 instead of ROW($A$1)?

The answer is: it is intended to show the derivation of that factor.
If the table were in A17:A21, you would write ROW($A$16) -- or simply
16, if perfer.

 

[joeu2004]

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.

 

[Wizard475]

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

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.