Using Excel To do "SOLVER"

Guys - See attached table..

What I want to do is optimize my team based on points and salaries with a salary restriction.

I need to have (2)PG (2) SG (2) SF (2) PF and (1) Center - - The players cannot be reused Meaning Blake Griffin cant play Center and PF

The goal is to create the MOST OPTIMAL TEAM with the above constraints as well as a salary of $90,000

I for the most part have it except not picking the same person twice

How can I get it so they won't pick a player twice

By changing the layout and using a binary matrix.

Alf

What exactly does that mean? Changing layout and using binary matrix

WIN BIG.xlsxRal Cat can you help me apply it to this table that I am inserting ?

I have attached the file but Cannot figure this out. I want to make sure that the solver doesn't place a "1" next to any name twice.

What exactly does that mean? Changing layout and using binary matrix

Put player's name in cell A2 and down as far as you need.

In columns B, C, D and E you define the positions the players can have with the help of binary format.

Sum of binary for a player must be <=1

Alf

How do you define the postitions with a binary format.

The players from Blake Griffin to Sergen Ibaka can all play position PF or position C. So range B2 to C10 is a range where solver can change cells. Since his is a case of either or this range must be set as binary (only 0 or 1 allowed) and the formula in F2 = SUM(B2:C2) .

Since Blake Griffin can only play PF or C or neither a constraint must be set on cell F2 i.e. it’s value must be <=1.

You just add the different ranges where solver can change cells and define them as binary and set the appropriate sum constraint for the cells in the F column.

As you only wish to pick 2 PG, 1 C, 2 SG and 2 PF you can get rid of the players who only can play SF and where a player can be SF or if he can play SF and PF get rid of the SF part as this will make your model a bit simpler to work with.

Alf

Assuming that sell-by date has passed on this home work I’m uploading my solution for those who have an interest in solver problems.

If you wish to test it I would suggest you first run it the way I set it up.

To see the effect of an optimized solver settings clear range B2:E52 then go to options and tick the box for “Assumed Linear Model” and “Assume Non-Negative”

Both solutions are identical in regards of team cost and value of object function. The only difference is the shuffling of a name from PF to C / C to PF.

What Shakespeare said “a rose by any other name would smell as sweet” is proved by Solver. A name is not important what’s important is the quality i.e. value of the object function.

Alf