CODEWITHSUNDEEP
exceloptimizationexcel-formulasubsetsolver
ryangicn
ryangicn

Reputation: 1

Selecting the best subset to maximize profit given a constraint. Excel Optimization

The problem: maximize profit under acid constraint

I have multiple oil wells. Each one has its profit and acid content. There is a certain acid limit allowable for processing.

I want to find the best combination of wells that can produce the most profit under the acid constraint. See example data here

At first, I thought I would just sort by profit and select the most profitable wells. But you can have 2 lower profit wells (combined profit higher) combine to have the same acid content as a higher profit well. I looked into solver, but don't think it applies in this case, because I want to select a subset.

Any suggestions?

Upvotes: 0

Views: 238

Answers (2)

pnuts
pnuts

Reputation: 59485

To maximise Profit/Acid up to a maximum Acid of say 100 divide Profit by Acid and sort descending. Then run cumulative totals for both Profit and Acid - stopping immediately before Acid exceeds 100.

You might need a bit of juggling if the Acid total at the cutoff is a long way short of 100.

Upvotes: 1

Peter K.
Peter K.

Reputation: 960

You can use Solver for such problem.

For inspiration, see this answer on SU : https://superuser.com/questions/204925/excel-find-a-subset-of-numbers-that-add-to-a-given-total

So the subset question is obtained by designating an additional column with binary values (keep the well or do not keep) for Solver to complete, and you then maximize profit (using the sum-product with the column with binary values), and as constraints you have to add the acid constraint.

Upvotes: 0

Related Questions