Fantasy Sports Lineup Generator

aubieboy

New Member
Joined
Dec 29, 2016
Messages
3
I am trying to make a spreadsheet that generates all possible player combinations that meet the requirements. The criteria are a 6 player lineup, each player has it's own individual salary, and a $50,000 lineup salary cap. I narrowed down the player pool to 20 players and each lineup must contain 6 players. Any guidance on how this can be done on excel?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
n!/(r!(n−r)!)

That's the formula you need for combinations. Where n is the total number of players to choose from (20) and r is the amount of players on a team (6). The total number is
38760.

If you were to put every possible team in individual rows, you'd have 1,938 rows for every team that starts with player 1 . Then 612 rows for every second player, 170 person third player, etc etc. You could map it all out and use a VLOOKUP to find the total team cost and display it next to the team. Then filter out the ones over the limit
 
Upvote 0
n!/(r!(n−r)!)

That's the formula you need for combinations. Where n is the total number of players to choose from (20) and r is the amount of players on a team (6). The total number is
38760.

If you were to put every possible team in individual rows, you'd have 1,938 rows for every team that starts with player 1 . Then 612 rows for every second player, 170 person third player, etc etc. You could map it all out and use a VLOOKUP to find the total team cost and display it next to the team. Then filter out the ones over the limit

Thanks! I'm pretty unfamiliar with Excel so a lot of terminology is new to me but attempting to figure it out. I was wondering is that math excluding repeating
sequence combinations? For example if players "a" "b" "c" "d" "e" "f" were a lineup, then "fedcba" would be discarded as it contains the same string of players
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top