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?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

WarfritLive

Board Regular
Joined
Sep 27, 2016
Messages
149
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
 

aubieboy

New Member
Joined
Dec 29, 2016
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

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
Top