SOLVED

# Calculate a capacity-adjusted demand allocation according to a ranking

Occasional Contributor

# Calculate a capacity-adjusted demand allocation according to a ranking

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 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Calculate a capacity-adjusted demand allocation according to a ranking

@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))))``

# Re: Calculate a capacity-adjusted demand allocation according to a ranking

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