SOLVED

Calculate a capacity-adjusted demand allocation according to a ranking

Occasional Contributor

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 ACol BCol CCol DCol E
Row 1RankOrig. AllocationSupplier capacityRemaining CapacityCapacity-adjusted Allocation
Row 22321,423,848396,832,07275,408,224to be calculated via formula
Row 36465,000,000465,000,000186,971,955 
Row 44300,963,566330,693,39329,729,827 
Row 51322,863,772375,000,00052,136,228 
Row 65292,370,729165,350,000(127,020,729) 
Row 73305,222-579175,000,000(185,225,166) 
Row 87-706,069,032- 
2 Replies
best response confirmed by Hans Vogelaar (MVP)
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))))

 

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