Fantasy Sports Lineup Generator

aubieboy

New Member
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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

WarfritLive

Board Regular
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
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

Replies
14
Views
575
Replies
2
Views
557
Replies
3
Views
559
Replies
6
Views
779
Replies
3
Views
515

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,893
Messages
5,834,273
Members
430,273
Latest member
Windrunner

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.

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

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