Forum Discussion
Solved
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 | - |
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))))
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.
