Logic Logistics Help

[models247]

Hi MrExcel members,

Thank you for reviewing this in advance.

Wanted to ask if anyone can help me out with a logic...

Okay so background:
I have 5 regions I would like to ship to. Let's rank them in terms of best price 1-5 for simplicity.
I want to create a formula that says... If I have 150 orders but can only fulfill 100 of them, I would like to optimize to the best channels (1 being best, 5 being worse, etc.).

So lets say 5 states and their corresponding price and amount of orders from that region
GA (1) - 50 orders
SC (2) - 30 orders
NC (3) - 20 orders
TN (4) - 0 orders
FL (5) - 0 orders

I would like to ship to the best location (GA) first for all my orders, and then SC, and so forth until I meet capacity (100). So I'd have to create a logic that captures the best price first and then send as many shipments up until its max and then move on to the next. Once I reach max capacity (100) then it has to stop.

I assumed this was pretty simple but sort of stuck/having a mental block I think I may be overcomplicating it in my head--anyway since my goofhead and figure it out, wanted to reach out to see if any of you guys would be able to help!

Thanks

 

[steve the fish]

Copy and paste the following into cell A1 of a sheet:

State	Max	Quantity
GA (1)	50
SC (2)	30
NC (3)	20
TN (4)	0
FL (5)	0

<tbody>
</tbody>

In cell E1 type your quantity. In cell C1 place this formula and copy down:

=IF($E$1>=SUM($B$2:B2),B2,MAX(0,$E$1-SUM($B$1:B1)))

 

[models247]

Copy and paste the following into cell A1 of a sheet:

State	Max	Quantity
GA (1)	50
SC (2)	30
NC (3)	20
TN (4)	0
FL (5)	0

<tbody>
</tbody>

In cell E1 type your quantity. In cell C1 place this formula and copy down:

=IF($E$1>=SUM($B$2:B2),B2,MAX(0,$E$1-SUM($B$1:B1)))

Thanks for the reply Steve. Actually I shouldn't have set it from highest to lowest. Currently that list is all over the place (e.g. TN (0), SC (30), FL (0), GA (50), etc.) Is there any way to do this as I feel it causes a circular reference. You think the Rank/Small functions could also help with picking up the best rank first? So lets say the maxes are scattered, how can I get it to pick up GA first until full, then SC, and so on to optimize?

Thanks again