Thought problem - working on a fantasy baseball lineup optimizer

[sampjennings]

So, guys and gals, I have no idea if this is possible using Excel. I play a historical fantasy baseball game called Diamond Mind, which uses historical statistics of players to and pits them against each other. I'm working on ways to optimize my roster construction using Excel spreadsheets, and have gotten pretty far, but haven't been able to push it to the next step.

I've got this boiled down to player name, salary, and total runs contributed from offensive and defensive statistics - in other words, the value in "runs" that a player will contribute in total, both from hitting and fielding. Each player, of course, has a different salary, and each league has a different budget.

What I'd like to do is figure out a way to get the computer to analyze my worksheet and test different lineups, finding the one that is optimized for total runs created for a given budget. Say, you input a budget of $60 million, computer tests every lineup combination and comes up with the best lineup given the available players and budget.

Anyone have an idea of where to start here? I can think of ways to do this with C++, but I'm pretty rusty on that and don't know how to export data from Excel to the program I would create.

 

[sampjennings]

Here's a link to the concerned worksheet - haven't input the catcher values yet, but I can do that. This is for a league where I am restricted to only using Negro Leagues players, so it doesn't have the full player pool, and since it's a Negro Leagues team, I didn't input catcher since Josh Gibson is far and away the best choice for that position.

https://www.dropbox.com/s/j9erplmgr5s3oj7/baseball.xls?dl=0