I have a spreadsheet for playing daily fantasy basketball; in this spreadsheet, I have the players' names, salaries, and projected fantasy points. I have set up solver to find the optimal lineup each night (highest projected fantasy point total) that fits in the constraints I have set. However, instead of only finding the top lineup each night, I want to run the optimizer to find the top "n" (n being a number I set Ex.10) number of lineups. The objective of the solver is to maximize the cell "AI2". I am not really sure how to find the top "n" number of lineups. I was thinking of running the solver once (giving me the maximum fantasy point total lineup) and then setting the goal to be the value of the same cell (AI2) subtracted by 2.5. I think this would ensure that I would get 10 different lineups at different intervals of score. Furthermore, I would like to have a separate sheet or range on the same sheet where excel automatically pastes in each of the lineups it creates. The problem with this whole task is that (1) I have no clue how I would create this loop in VBA), and (2) I don't know if this would even accomplish what I want excel to do. My VBA skills are extremely limited so I was hoping someone could help me out with some sample code to create the 10 optimal lineups and paste them in a table. I have looked online to see if anyone has answered my case about running solver multiple times, but all the threads I come across seem to rely on changing the objective cell's range (I just want the value in the same cell to change each time). Also, this may be incredibly complicated but I also wanted to know how, and if, I could set up a solver that factors in player exposures (Ex. LeBron James only appears in 50% of lineups). Finally, I was wondering if maybe this task would be better accomplished outside of excel, like by creating a program in python (or something like that). Any feedback/responses are much appreciated. Thanks.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
