maximize price for given consumption in a supply portfolio

In the attached excel, each row is a supply of specific price and quantity. I need a formula that will automatically find the maximum prices and maximize the total price, given that there is a limit of 230.000 units in the quantity that can be absorbed from the total of 276.000.

I.e. lets say that a customer consumed 230.000 units of quantity. If I want to maximize the cost of the customer, I would first choose the supply in row 14 (with price 33) so that I now have included 21.000 units. Then I will include the quantity with the next highest price (row 10). The formula must understand that the same price can be found in more than one instance in the table. I note this because some formulas i've tried, calculated the first instance of a double occurance two times, and left the second quantity out of the calculation.

Folowing that rule, at some point I will have added rows 2,3,6,7,10,11 and 14, which correspond to 186.000 units of quantity. I need 230.000 - 186.000 = 44.000 more, which should be added from the row with the next highest price (row 4, price 27).

Finally the calculation that I need to take place is the following:
(33*21000 + 32*23000 + 31*10000 + 30*19000 + 29*29000 + 28*43500 + 28*40500 + 27*44000) / 230000 = 29,09

Please note that some rows might be blank. Take this into account so that the formula does not return #n/a message or otherwise.

Thanks,
Titus

Try this

Please Login or Register  to view this content.

Thanks, it worked!

Following on the previous message, is there a forumula that will produce a table with 3 columns (i.e. item, quantity, price) based on the same maximazation principle?
Thanks in advance.
Titus

Another way,

Please Login or Register  to view this content.