Excel Solver with Multiple Input Variables

Question

I have an Excel sheet that has a list of sports players, their positions, a salary assigned to those players, and a projected amount of points.

I'm currently using solver to create the best group of sports players possible with certain position restrictions and within a predefined salary cap.

In my specific example, the different player positions are: PG, SG, SF, PF, C, G, and F. Note that a G can be either a PG or a SG, and a F can be either a SF or PF.

This solver is working currently, but in some cases a player might have multiple positions. So, for example, Player A can be used as a SF OR a SG.

Is there any way to account for this within a Solver function so that the player in question can be used in either slot?

To help explain, I'll provide some examples of the data being used. Here is a small sample of the player list:

Position Player             Salary     Game                Points   Pos 1   Pos 2
PF/C    Kevin Love          $10,400     Pho@Min 09:30PM ET  53.17    PF      C
PG/SG   Stephen Curry       $10,000     GS@Bkn 07:30PM ET   47.50    PG      SG
SG/SF   James Harden        $9,500  LAL@Hou 08:00PM ET  41.13    SG      SF 
PF/C    LaMarcus Aldridge   $9,500  Orl@Por 10:00PM ET  45.22    PF      C
PF/C    Anthony Davis       $9,200  Was@NO 08:00PM ET   42.97    PF      C
PF/C    Blake Griffin       $9,000  Bos@LAC 10:30PM ET  42.66    PF      C
PG      John Wall           $8,900  Was@NO 08:00PM ET   42.09    PG
PF/C    Dwight Howard       $8,700  LAL@Hou 08:00PM ET  41.19    PF      C
SG/SF   Paul George         $8,600  Ind@Atl 07:30PM ET  40.06    SG      SF
PF      Paul Millsap        $8,400  Ind@Atl 07:30PM ET  37.96    PF
PF/C    Al Horford          $8,300  Ind@Atl 07:30PM ET  37.33    PF      C

To solve the G and F issue, I have a list that tallies the "selected" players, and that list has a min/max of each. So in this example I need 1 PG, 1 SG, and 1 G. So I have a PG column with a min of 1 and a max of 2, a SG column with a min of 1 and a max of 2, and a G column, which adds PG/SG together that has a min of 3 and a max of 3. Then part of the solver criteria is all the min/max must match.

I have split the first column into POS1 and POS2 to separate the two possible positions, but I can't figure out how to include both in the solver function. For a player like Kevin Love, I want the solver to take into account that he can be placed in either the PF, F, or C spots.

Here is that table:

    PG  SG  SF  PF  C   G   F   Ttl
Min 1   1   1   1   1   3   3   8
Max 3   3   3   3   2   4   4   8
Cur 1   3   1   2   1   4   3   8

So for example, the Solver criteria is:

Sum of Salary <= Salary Cap (50000)
PG Cur >= PG Min
Repeat for all Min
PG Cur <= PG Max
Repeat for all Max
Maximize Points

And finally, here's an example of a solution:

PG   Stephen Curry    $10,000     47.5
SG   James Harden     $9,500      41.13
SF   Paul George      $8,600      40.06
PF   Dwight Howard    $8,700      41.19
C    Blake Griffin    $9,000      42.66
G    John Wall        $8,900      42.09
F    Al Horford       $8,300      37.33
Total                 $63,000     292.5

Each player fits into their slot because one of their two positions (POS1 or POS2) fits the criteria. Obviously the total doesn't fit, so this wouldn't be a viable solution, but it's just an example.

Hopefully I've provided enough details, but if not please let me know and I'll be happy to explain further. Thanks in advance.

Can you upload an example of what the ideal result is? And also update your table with the splits that you mentioned in the first column? And give some details around the solver you are using currently? That will help answerers direct their efforts. — guitarthrower
– guitarthrower, Commented Jan 9, 2014 at 16:45
thanks for the feedback. I updated the original question...does that seem better? — fullOfQuestions
– fullOfQuestions, Commented Jan 9, 2014 at 17:44

Sam · Accepted Answer · 2014-01-09 20:45:24Z

1

Your question is still a bit vague because your Salary Cap is $50,000 but you show $63,000 of players selected.

Assumptions:
1. You want 5 players since that is the number of players floor and the only way to get a team consisting of your player-list below $50,000. This is probably Fantasy hoops and you want 7 or 8, but the solution should still work for 8 players with either a broader list of options and lower salaries.
2. I'll also assume that there is little-no VBA in here since you didn't provide any - or at least none of that needs to be adjusted.

My solution (which is still in testing) is to throw/cast (or VBA equivalent) for Pos1 and Pos2 to a text of position number (i.e., 1 = PG, 2=SG, etc.).

Next, =concatenate(Pos1, Pos2) so that a PG is an 11, a PG/SG is a 12, etc. ("G" is also an 11 in this scenario which might put a glitch in my logic).

Change your Position Table with Min/Max/Cur values looks something like this:

Pos.    | PG | G  | PG/SG | SG | SG/SF | SF/PF | F  | PF | PF/C | C  
--------------------------------------------------------------------  
Pos1    | 1  | 1  | 1     | 2  | 2     | 3     | 3  | 4  | 4    | 5  
Pos2    | 1  | 2  | 2     | 2  | 3     | 4     | 4  | 4  | 5    | 5  
PosNbr  | 11 | 12 | 12    | 22 | 23    | 34    | 34 | 44 | 45   | 55  
--------------------------------------------------------------------  
Min     | 1  | 3  | 3     | 1  |       | 3     | 4  | 1  |      | 1  
Max     | 3  | 4  | 4     | 3  |       | 4     | 4  | 3  |      | 2  
Curr    | 1  | 1  | 1     | 0  | 0     | 0     | 0  | 0  | 3    | 0

(values are my own).

edited Jan 9, 2014 at 20:45

answered Jan 9, 2014 at 19:28

Sam

4971 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fullOfQuestions Over a year ago

So I didn't user your exact solution, but your response sparked a simple answer for me. I just left the listed position as PG/SG, SG/SF, etc and added new Min/Max numbers for them. So in addition to the min/max for PG/SG/SF/PF/C, I have a min on the PG/SG of 0 and a max of 5 (the roster requirements allow up to 5 - PG, SG, G, and 2 "any player" spots). Works like a charm! Thanks for your respnse, it helped me figure it out!

Sam Over a year ago

Glad to be of service. Your question sparked my inquiry into "Solver" which I have not used previously.

Collectives™ on Stack Overflow

Excel Solver with Multiple Input Variables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related