Assign Percentages to Dynamic Ranks with Max and Min constraints

[tjkramer]

I have 6 regions (Region A thru Region F). I have ranked each region based on population, with the most populated being ranked #1, and the least being #6. I want to "allocate" dollars to each of the 6 regions based on percentages. Each of the Regions A thru F has a maximum percentage that can be allocated and a minimum percentage that can be allocated. These maximum and minimum percentages may be changed, but the overall dollars to be allocated stays fixed. I need to write a formula that allocates the highest percentage/dollars to the region ranked #1, but stays within the Max/Min of that region, and then allocates to the second-ranked region and stays within the Max/min of that region, and continues to allocate to each of the 6 regions. Total allocation cannot be >100% and every allocation must be within the min/max. Any help would be greatly appreciated. Looking for an excel formula and not VisBasic or others.

 

[FormulasOnly]

Upper area shows an approach for unsorted ranks, lower area is easier by far in case ranks are sorted (grey cells need to be kept empty):

MrExcel_Assign_Percentages_to_Dynamic_Ranks_with_Max_and_Min_connstraints.xlsx
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
2							Allocated						Total
3	Hub	Min	Max		Rank	Empty!	1	2	3	4	5	6	100,00%
4	A	10,00%	35,00%		4		0,00%	0,00%	0,00%	10,00%	0,00%	0,00%	10,00%
5	B	5,00%	35,00%		2		0,00%	35,00%	0,00%	0,00%	0,00%	0,00%	35,00%
6	C	5,00%	20,00%		6		0,00%	0,00%	0,00%	0,00%	0,00%	5,00%	5,00%
7	D	5,00%	20,00%		5		0,00%	0,00%	0,00%	0,00%	5,00%	0,00%	5,00%
8	E	5,00%	30,00%		1		30,00%	0,00%	0,00%	0,00%	0,00%	0,00%	30,00%
9	F	5,00%	35,00%		3		0,00%	0,00%	15,00%	0,00%	0,00%	0,00%	15,00%
10
11						Allocated
12	Hub	Min	Max		Rank
13	E	5,00%	30,00%		1	30,00%
14	B	5,00%	35,00%		2	35,00%
15	F	5,00%	35,00%		3	15,00%
16	A	10,00%	35,00%		4	10,00%
17	D	5,00%	20,00%		5	5,00%
18	C	5,00%	20,00%		6	5,00%
19
Tabelle1

Cell Formulas
Range	Formula
AB3	AB3	=SUM(AB4:AB9)
V4:AA9	V4	=IF(V$3=$T4,MIN($R4,100%-SUM($U$4:U$9)-SUMIFS($Q$4:$Q$9,$T$4:$T$9,">"&$T4)),0)
AB4:AB9	AB4	=SUM(V4:AA4)
U13:U18	U13	=MIN($R13,100%-SUM($Q14:$Q$19)-SUM($U$12:$U12))

 

[tjkramer]

Thank you very much for the prompt reply and the excellent, easy-to-understand answer. Worked like a charm. You saved me a ton of time. Sincerely appreciate your efforts and professionalism. Thanks again!