Optimize Basketball Lineup

Hi All - have read a few posts on optimization but have not found anything that will solve my issue.

1 - attached is the excel spreadsheet of data I am referring to.

2 - I would like to be able to maximize the projected points while staying under a set salary - i.e. $60K

3 - I need to run every possible lineup to achieve the max result.

4 - Lineup must have 2 PG, 2 SG, 2 SF, 2 PF, and 1 C (and again total $$ must stay under $60K)

5 - An option for the next 5-10 best lineups would be great as well but I will not push it...lol.

Perhaps you could use solver?

http://www.excelforum.com/excel-form...-cap-help.html

In post #12 there is a model using solver.

Alf

Hey Alf - i did read this thread and didn't think it was exactly what I needed.

Either way - i gave it a go and looks like I am almost there.

Right now the solver is saying it cannot find a feasible solution.

Would you mind taking a look at it? I must be making a minor mistake (or major since I have never used the solver before.)

Thanks,
Rob

Hey Alf - I am good - figured it out - or at least it is working.

Had to click on linear and lower the tolerance...

Thanks,
Rob

Glad you solved it but there must be more to the changes you made than changing model to linear and lower tolerance.

Looking at your uploaded model I can't find the "sorting mechanism" i.e. when solver picks a player from the A column the result should be "transferred" to the appropriate "PG", "SG", "SF", "PF" and "C" column so that solver pick the required number of players from each group.

This I set by a formula "=A??*1" in the appropriate column. See my uploaded file tab "Solver_model" for a better explanation.

I've also set up a solver model for your original file "layout" both model works and give the same result but it's easier to set up the model with all players in one column.

It's also possible to do a rerun finding the second best team. If you delete the formulas in cell F4 and F7 then solver can't select these two players because the sum of players in the F column should be 2. So deleting the formulas for the selected players in the first solver run solver will then pick the second best combination at the second run and so fort.

It is possible to write a macro that runs solver in a loop where result is copied then formulas for the selected players are deleted, solver do a rerun result copied formulas deleted, solver do a rerun ......

Alf

Hey Alf,

Ya I figured the "sorting mechanism" - used =+IF($E2="PG",$A2*1,IF(OR($E2="SG",$E2="SF",$E2="PF",$E2="C"),"",""))

Substitute PG for SG in the SG column - etc...

It would be nice to have it post the optimal lineup on the page somewhere - probably easy enough - just haven't had time the last few days.

Would also be nice to post say - the top 10 lineups by points - don't want to eliminate players just want the next 9 highest point totals - irrelevant of the players.

Again - thanks - you definitely pointed me into the right direction.

You are welcome.

If this solves you problem could you please mark this thread "Solved"

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved