CODEWITHSUNDEEP
exceloptimizationexcel-formulavba
Jeff Settle
Jeff Settle

Reputation: 1

Optimization of a list in Excel with Variables

I have a list of 153 golfers with associated salaries and average scores.

I want to find the combination of 6 golfers that optimizes avg score and keeps salary under $50,000.

I've tried using Solver, but I am stuck! Can anyone help please? :)

Upvotes: 0

Views: 2869

Answers (2)

Erwin Kalvelagen
Erwin Kalvelagen

Reputation: 16724

This is a simple optimization problem that can be solved using Excel solver (just use "Simplex Lp solver" -- somewhat of a misnomer as we will use it here to solve an integer programming or MIP problem).

You need one column with 153 binary (BIN) variables (Excels limit is I believe 200). Make sure you add a constraint to set the values to Binary. Lets call this column INCLUDE; Solver will fill it with 0 or 1 values. Sum these values, and add a constraint with SUMINCLUDE=6. Then add a column with INCLUDE * SCORE. Sum this column and this is your objective (optimizing the average is the same as optimizing the sum). Then add a column with INCLUDE*SALARY and sum these. Add a constraint with SUMSALARY <= 50k. Press solve and done.

I don't agree with claims that Excel will crash on this or that this does not fit within the limits of Excels solver. (I really tried this out).

I prefer the simplex method above the evolutionary solver as the simplex solver is more suitable for this problem: it is faster (simplex takes < 1 seconds) and provides optimal solutions (evolutionary solver gives often suboptimal solutions).

If you want to solve this problem with Matlab a function to look at is intlinprog (Optimization Toolbox).

To be complete: this is the mathematical model we are solving here:

enter image description here

Results with random data:

enter image description here

....

enter image description here

Upvotes: 1

OldUgly
OldUgly

Reputation: 2119

Illustrating a solution that is pretty close to what @ErwinKalvelagen suggested.

  1. Column A is the names of the 153 golfers
  2. Column B is the golfers salaries (generated by =RANDBETWEEN(50, 125)*100, filled down, then Copy/Paste Values)
  3. Column C is the golfers average scores (generated by =RANDBETWEEN(70, 85), filled down, then Copy/Paste Values)
  4. Column D is a 0 or 1 to indicate if the golfer is included.
  5. Cell F2 is the total salary, given by =SUMPRODUCT(B2:B154,D2:D154)
  6. Cell G2 is the number of golfers, given by =SUM(D2:D154)
  7. Cell H2 is the average score of the team, given by =SUMPRODUCT(C2:C154,D2:D154)/G2

The page looks like this, before setting up Solver ...

enter image description here

The Solver setup looks like this ...

enter image description here

According to the help, it says to use Evolutionary engine for non-smooth problems. In Options, I needed to increase the Maximum Time without improvement from 30 to 300 (60 may have been good enough).

enter image description here

It took a couple of minutes for it to complete. It reached the solution of 70 fairly quickly, but spent more time looking for a better answer.

enter image description here

And here are the six golfers it came up with.

enter image description here

Of the golfers with an average of 70, it could have found a lower salary.

enter image description here

In Cell I2 added the formula =F2+F2*(H2-70) which is essentially salary penalized by increases in average score above 70 ...

enter image description here

... and use the same Solver setup, except to minimize Cell I2 instead of H2 ...

enter image description here

and these are the golfers it chose ...

enter image description here

Again - it looks like there is still a better solution. It could have picked Name97 instead of Name96.

Upvotes: 2

Related Questions