Calculate a capacity-adjusted demand allocation according to a ranking

[BDB1973]

In this scenario, only 6 suppliers of 7 will receive demand. The goal is for the adjusted demand allocations to be assigned, by a formula, to the highest ranked suppliers, up to their capacities, until there is no more demand to be allocated.  I'm exhausted from using multiple IF statements with AND and OR and MIN/MIX and anything else I can think of.  Appreciate the assist.

	Col A	Col B	Col C	Col D	Col E
Row 1	Rank	Orig. Allocation	Supplier capacity	Remaining Capacity	Capacity-adjusted Allocation
Row 2	2	321,423,848	396,832,072	75,408,224	to be calculated via formula
Row 3	6	465,000,000	465,000,000	186,971,955
Row 4	4	300,963,566	330,693,393	29,729,827
Row 5	1	322,863,772	375,000,000	52,136,228
Row 6	5	292,370,729	165,350,000	(127,020,729)
Row 7	3	305,222-579	175,000,000	(185,225,166)
Row 8	7	-	706,069,032	-

[#2 · Accepted solution]

@BDB1973 Not sure I understand but perhaps you should start by sorting by rank and then us a fairly straight-forward formula to allocate the total amount that I placed in B11. See attached.

=MAX(0,(MIN(C2,$B$11-SUM($C$1:C1))))

[#3]

@Riny_van_Eekelen: This solved my issue. Many times, simpler is better. Thank you so much.