Calculating combinations

[Reboshua]

I've recently started playing daily fantasy sports (DFS) and am curious how I can figure out some of the combinations of players using excel. For those unfamiliar with DFS, participants (me) pick a new team of players each day and compete with other participants (you) based on the performance of the players we select to be on our team. Using NBA as an example, I may pick LeBron James to be on my team and you might pick Kobe Bryant (you actually pick 9 players, but I'm simplifying.) Whoever scores more points that night will win the contest.

More details relevant to excel problem:

1. Each lineup I create must have 9 players on it, consisting of the following:

- 2 Point Guards (PG)
- 2 Shooting Guards (SG)
- 2 Small Forwards (SF)
- 2 Power Forwards (PF)
- 1 Center (C)

2. Each player has a salary/price associated with them for that day's contest.
3. I am constrained by a $60,000 salary cap each day.

So, you need to pick a lineup of players based on the structure described in #1, with the constraint of not going over the salary cap.

What I'm trying to use excel to figure out is the number of total combinations there might be for any given day. Tonight, for example, there are 370 players (see data below). Additionally, I'd like to be able to reduce the number of total combinations by introducing additional constraints on the problem based on other data I've collected. For example, I might want my minimum total salary spent to be $59,000 and I might want the minimum projected points for that evening (not shown below) to be 250, etc.

I know I can use solver to get at some of this, but I'm really interested in knowing the number of possible combinations when I introduce constraints.

Position	Player			Team	Salary
PG	Russell Westbrook			OKC	13200
PF	Anthony Davis			NOP	12400
SF	LeBron James			CLE	10900
C	DeMarcus Cousins			SAC	10900
SF	Kevin Durant			OKC	10300
PG	Chris Paul			LAC	10200
PG	Stephen Curry			GSW	10000
PF	LaMarcus Aldridge			POR	9600
PG	John Wall			WAS	9600
PF	Blake Griffin			LAC	9300
C	DeAndre Jordan			LAC	9100
PF	Pau Gasol			CHI	8900
SG	Jimmy Butler			CHI	8600
PG	Kyrie Irving			CLE	8500
SG	Dwyane Wade			MIA	8400
SG	Victor Oladipo			ORL	8400
PG	Tyreke Evans			NOP	8300
SF	Rudy Gay			SAC	8300
C	Hassan Whiteside			MIA	8200
PG	Damian Lillard			POR	8200
PG	Kyle Lowry			TOR	8200
SF	Kawhi Leonard			SAS	8200
C	Nikola Vucevic			ORL	8100
PG	Jrue Holiday			NOP	8000
C	Al Jefferson			CHA	8000
PF	Serge Ibaka			OKC	8000
PF	Zach Randolph			MEM	7900
C	Marc Gasol			MEM	7900
PF	Paul Millsap			ATL	7800
PF	Kevin Love			CLE	7700
SF	Draymond Green			GSW	7700
C	Al Horford			ATL	7700
PF	Tim Duncan			SAS	7700
PG	Michael Carter-Williams			MIL	7500
PG	Ty Lawson			DEN	7500
SG	DeMar DeRozan			TOR	7400
PG	Isaiah Thomas			BOS	7300
PF	Nerlens Noel			PHI	7300
SG	Klay Thompson			GSW	7200
SG	Giannis Antetokounmpo			MIL	7200
PG	Mo Williams			CHA	7000
SG	Monta Ellis			DAL	7000
PF	Nikola Mirotic			CHI	7000
PG	Goran Dragic			MIA	7000
PG	Alexey Shved			NYK	7000
PG	Elfrid Payton			ORL	6900
SF	Nicolas Batum			POR	6900
C	Brook Lopez			BRK	6900
PF	Kenneth Faried			DEN	6900
PG	Kemba Walker			CHA	6800
PG	Jeff Teague			ATL	6700
C	Enes Kanter			OKC	6700
C	Marcin Gortat			WAS	6700
C	Joakim Noah			CHI	6700
SG	J.J. Redick			LAC	6600
SF	Tobias Harris			ORL	6500
SF	Danilo Gallinari			DEN	6400
PG	Tony Parker			SAS	6400
SF	Khris Middleton			MIL	6400
SF	Chandler Parsons			DAL	6400
PG	George Hill			IND	6400
PG	Darren Collison			SAC	6300
PG	Mike Conley			MEM	6300
SG	Wesley Matthews			POR	6200
SG	Rodney Stuckey			IND	6100
SG	Eric Gordon			NOP	6000
C	Jonas Valanciunas			TOR	6000
SG	Avery Bradley			BOS	6000
SG	Will Barton			DEN	6000
PF	Dirk Nowitzki			DAL	6000
SF	Luol Deng			MIA	5900
PG	Rajon Rondo			DAL	5900
PF	Andrea Bargnani			NYK	5900
SF	Michael Kidd-Gilchrist			CHA	5900
SG	Evan Turner			BOS	5900
PF	Thaddeus Young			BRK	5800
PG	Deron Williams			BRK	5800
SF	DeMarre Carroll			ATL	5800
SG	Brad Beal			WAS	5800
PG	Aaron Brooks			CHI	5800
SF	Wilson Chandler			DEN	5700
PF	David West			IND	5700
SG	Danny Green			SAS	5700
PF	Brandon Bass			BOS	5700
C	Tyson Chandler			DAL	5600
PG	Jarrett Jack			BRK	5600
SG	J.R. Smith			CLE	5600
PG	Jameer Nelson			DEN	5500
PG	Ishmael Smith			PHI	5500
SG	Jamal Crawford			LAC	5500
C	Omer Asik			NOP	5500
PF	Ersan Ilyasova			MIL	5400
SG	Joe Johnson			BRK	5400
SF	Gerald Henderson			CHA	5400
PG	Marcus Smart			BOS	5400
SG	Kent Bazemore			ATL	5300
SF	Robert Covington			PHI	5300
PF	Ryan Anderson			NOP	5300
PG	D.J. Augustin			OKC	5200
PG	Dennis Schroder			ATL	5200
SF	Andre Iguodala			GSW	5200
C	Zaza Pachulia			MIL	5200
SF	Matt Barnes			LAC	5200
SF	Jae Crowder			BOS	5100
C	Steven Adams			OKC	5100
C	Roy Hibbert			IND	5100
PG	Ray McCallum			SAC	5000
C	Andrew Bogut			GSW	5000
SF	Jeff Green			MEM	5000
PG	Langston Galloway			NYK	5000
C	Timofey Mozgov			CLE	4900
C	Robin Lopez			POR	4900
SF	Luc Richard Mbah a Moute			PHI	4900
PG	Nate Robinson			LAC	4800
PG	Isaiah Canaan			PHI	4800
PF	Taj Gibson			CHI	4800
C	Tyler Zeller			BOS	4800
SF	Paul Pierce			WAS	4800
C	Jusuf Nurkic			DEN	4800
SG	Louis Williams			TOR	4800
PG	Jose Calderon			NYK	4700
SG	Tony Allen			MEM	4700
PF	Cody Zeller			CHA	4700
PF	John Henson			MIL	4700
C	Alexis Ajinca			NOP	4700
SG	Randy Foye			DEN	4600
SF	Al-Farouq Aminu			DAL	4600
PG	Mario Chalmers			MIA	4600
PF	Amir Johnson			TOR	4600
PF	Luis Scola			IND	4600
PF	Nene Hilario			WAS	4600
SG	Kyle Korver			ATL	4600
SG	O.J. Mayo			MIL	4500
SF	C.J. Miles			IND	4500
PF	Thomas Robinson			PHI	4500
C	Anderson Varejao			CLE	4500
PF	Louis Amundson			NYK	4500
PF	David Lee			GSW	4500
PF	Tristan Thompson			CLE	4500
C	Cole Aldrich			NYK	4400
SF	Justin Holiday			GSW	4400
C	Mason Plumlee			BRK	4400
C	Kelly Olynyk			BOS	4400
PG	Andre Miller			SAC	4400
SG	Manu Ginobili			SAS	4400
PG	Jerryd Bayless			MIL	4300
PF	Patrick Patterson			TOR	4300
SF	Harrison Barnes			GSW	4300
SG	Tim Hardaway Jr.			NYK	4300
SG	Dion Waiters			OKC	4300
PF	Jason Thompson			SAC	4300
SG	Anthony Morrow			OKC	4300
SG	Jason Richardson			PHI	4300
PG	Devin Harris			DAL	4300
PF	Amar'e Stoudemire			DAL	4200
C	Tiago Splitter			SAS	4200
SF	Mike Dunleavy			CHI	4200
SG	Arron Afflalo			POR	4200
PF	Marvin Williams			CHA	4200
SG	Henry Walker			MIA	4200
SG	Tony Snell			CHI	4200
PG	C.J. Watson			IND	4200
PG	Shaun Livingston			GSW	4200
PF	Dante Cunningham			NOP	4100
SF	Quincy Pondexter			NOP	4100
C	Chris Kaman			POR	4100
SF	Solomon Hill			IND	4100
SF	Hollis Thompson			PHI	4100
SG	Iman Shumpert			CLE	4100
PF	J.J. Hickson			DEN	4100
PF	Kris Humphries			WAS	4100
SG	Ben McLemore			SAC	4100
C	Bismack Biyombo			CHA	4000
PG	Greivis Vasquez			TOR	4000
SG	Evan Fournier			ORL	4000
PF	Boris Diaw			SAS	4000
SG	Lance Stephenson			CHA	4000
SG	Tyler Johnson			MIA	4000
PF	Jason Smith			NYK	4000
PG	Norris Cole			NOP	4000
C	Chris Andersen			MIA	3900
PG	Jose Juan Barea			DAL	3900
PG	Beno Udrih			MEM	3900
SF	Maurice Harkless			ORL	3800
SF	Jared Dudley			MIL	3800
PF	Jarnell Stokes			MEM	3800
C	Spencer Hawes			LAC	3800
PG	Shelvin Mack			ATL	3800
PF	Drew Gooden			WAS	3800
PF	Mike Scott			ATL	3700
SF	Michael Beasley			MIA	3700
C	Samuel Dalembert			NYK	3700
PF	Marreese Speights			GSW	3700
PF	Channing Frye			ORL	3700
SF	Hedo Turkoglu			LAC	3700
SF	Bojan Bogdanovic			BRK	3700
SF	Omri Casspi			SAC	3700
SG	Markel Brown			BRK	3700
SG	Courtney Lee			MEM	3700
C	Dewayne Dedmon			ORL	3700
C	Miles Plumlee			MIL	3600
SF	Lance Thomas			NYK	3600
PG	Shane Larkin			NYK	3600
PF	Reggie Evans			SAC	3600
C	Ian Mahinmi			IND	3600
SG	Leandro Barbosa			GSW	3600
SF	Vince Carter			MEM	3600
PG	Nick Calathes			MEM	3600
SF	Austin Daye			ATL	3600
PF	Kenyon Martin			MIL	3600
PF	Charlie Villanueva			DAL	3600
SF	James Johnson			TOR	3600
PF	Jerami Grant			PHI	3600
PG	Tyler Ennis			MIL	3600
SG	Marco Belinelli			SAS	3600
C	Festus Ezeli			GSW	3500
C	Aron Baynes			SAS	3500
SG	Sergey Karasev			BRK	3500
C	Lucas Nogueira			TOR	3500
SF	Jeffery Taylor			CHA	3500
SG	Jarell Eddie			ATL	3500
SF	Cleanthony Early			NYK	3500
SG	Gary Harris			DEN	3500
SG	Ricky Ledo			NYK	3500
SG	Nemanja Nedovic			GSW	3500
SG	Joe Harris			CLE	3500
SG	John Jenkins			ATL	3500
SF	Luigi Datome			BOS	3500
PF	Dwight Powell			DAL	3500
SF	James Young			BOS	3500
SF	Bruno Caboclo			TOR	3500
PF	Jakarr Sampson			PHI	3500
SG	Zoran Dragic			MIA	3500
SF	Damjan Rudez			IND	3500
PF	Noah Vonleh			CHA	3500
PF	Aaron Gordon			ORL	3500
PF	Travis Wear			NYK	3500
PG	Gal Mekel			NOP	3500
SG	Erick Green			DEN	3500
PG	Larry Drew II			PHI	3500
C	Pero Antic			ATL	3500
PF	Joffrey Lauvergne			DEN	3500
PG	Kalin Lucas			MEM	3500
SG	Alan Anderson			BRK	3500
PG	Toure' Murry			WAS	3500
C	Shayne Whittington			IND	3500
C	Ognjen Kuzmic			GSW	3500
PF	Cameron Bairstow			CHI	3500
SF	Victor Claver			DEN	3500
PF	Furkan Aldemir			PHI	3500
PG	Troy Daniels			CHA	3500
PG	Nate Wolters			NOP	3500
SG	C.J. McCollum			POR	3500
SF	Doug McDermott			CHI	3500
PG	Will Cherry			CLE	3500
C	Alex Kirk			NYK	3500
PF	Mike Muscala			ATL	3500
PG	Matthew Dellavedova			CLE	3500
C	Joel Freeland			POR	3500
SF	Chris Copeland			IND	3500
PF	Malcolm Thomas			PHI	3500
PF	Greg Smith			DAL	3500
PF	Mitch McGary			OKC	3500
SF	Glenn Robinson III			PHI	3500
PG	David Stockton			SAC	3500
SG	C.J. Wilcox			LAC	3500
SG	Nik Stauskas			SAC	3500
PF	Andrew Nicholson			ORL	3500
PG	Brian Roberts			CHA	3500
SG	Jordan Adams			MEM	3500
SF	Drew Gordon			PHI	3500
SF	James Ennis			MIA	3500
PF	Kyle O'Quinn			ORL	3500
SF	Ronald Roberts, Jr.			PHI	3500
SF	Kyle Anderson			SAS	3500
SF	Terrence Ross			TOR	3500
PF	Kevin Seraphin			WAS	3500
SF	Martell Webster			WAS	3500
SF	Richard Jefferson			DAL	3500
C	Matt Bonner			SAS	3500
SF	Andrei Kirilenko			PHI	3500
PG	Steve Blake			POR	3500
PF	Elton Brand			ATL	3500
SF	Chris Douglas-Roberts			BOS	3500
SG	Thabo Sefolosha			ATL	3500
PF	Nick Collison			OKC	3500
SG	Willie Green			ORL	3500
C	Kosta Koufos			MEM	3500
SF	Mike Miller			CLE	3500
C	Nazr Mohammed			CHI	3500
SF	Steve Novak			OKC	3500
PG	Patrick Mills			SAS	3500
PF	Jeff Ayres			SAS	3500
SF	James Jones			CLE	3500
SF	Dorell Wright			POR	3500
C	JaVale McGee			PHI	3500
C	Brendan Haywood			CLE	3500
PG	Toney Douglas			NOP	3500
PG	A.J. Price			CLE	3500
C	Ryan Hollins			SAC	3500
PG	Ramon Sessions			WAS	3500
PF	Tyrus Thomas			MEM	3500
SF	Shawn Marion			CLE	3500
SG	Ben Gordon			ORL	3500
C	Jason Maxiell			CHA	3500
PG	Kirk Hinrich			CHI	3500
PG	Jannero Pargo			CHA	3500
PF	Glen Davis			LAC	3500
C	Kendrick Perkins			CLE	3500
PG	Raymond Felton			DAL	3500
SF	Gerald Wallace			BOS	3500
C	Ronny Turiaf			PHI	3500
PF	Carl Landry			SAC	3500
PF	Darrell Arthur			DEN	3500
PF	Udonis Haslem			MIA	3500
PF	Shavlik Randolph			BOS	3500
PG	Luke Ridnour			ORL	3500
SF	Rasual Butler			WAS	3500
PF	Tyler Hansbrough			TOR	3500
C	Chuck Hayes			TOR	3500
SG	Dahntay Jones			LAC	3500
SG	Brandon Rush			GSW	3500
C	DeJuan Blair			WAS	3500
PF	Jonas Jerebko			BOS	3500
SG	Andre Dawkins			BOS	3500
SG	Austin Rivers			LAC	3500
C	Patric Young			NOP	3500
C	Bernard James			DAL	3500
PG	Shabazz Napier			MIA	3500
SF	Jeremy Lamb			OKC	3500
SF	Perry Jones III			OKC	3500
SG	Allen Crabbe			POR	3500
PG	Jorge Gutierrez			MIL	3500
SG	Andre Roberson			OKC	3500
SF	Otto Porter			WAS	3500
C	Henry Sims			PHI	3500
C	Johnny O'Bryant			MIL	3500
PG	Phil (Flip) Pressey			BOS	3500
SG	P.J. Hairston			CHA	3500
PF	James Michael McAdoo			GSW	3500
PG	Russ Smith			MEM	3500
SG	Darius Miller			NOP	3500
C	Meyers Leonard			POR	3500
SG	Roy Devyn Marble			ORL	3500
C	Jeff Withey			NOP	3500
PF	Cory Jefferson			BRK	3500
SF	Quincy Acy			NYK	3500
SG	Elliot Williams			NOP	3500
SF	Landry Fields			TOR	3500
C	Jerome Jordan			BRK	3500
PG	Donald Sloan			IND	3500
SF	Luke Babbitt			NOP	3500
C	Ekpe Udoh			LAC	3500
PG	Garrett Temple			WAS	3500
SG	Reggie Williams			SAS	3500
SF	Alonzo Gee			POR	3500
C	Greg Stiemsma			TOR	3500
PF	Derrick Williams			SAC	3500
PG	Jimmer Fredette			NOP	3500
SG	Malcolm Lee			PHI	3500
C	Lavoy Allen			IND	3500
PG	E'Twaun Moore			CHI	3500
SF	JaMychal Green			MEM	3500
PG	Darius Morris			BRK	3500
PF	Jon Leuer			MEM	3500
SF	Jordan Hamilton			LAC	3500
PG	Cory Joseph			SAS	3500
SF	Kyle Singler			OKC	3500
SG	Jared Cunningham			PHI	3500

<tbody>
</tbody>

 

[West Man]

I think you will find the number of combinations too large to work with. For example if you eliminate all with a salary of 3700 and below, without a spending cap you have

12,061,815,296 combinations

<tbody>
</tbody>

 

[Reboshua]

Thanks West Man. I've definitely run the combos possible unconstrained. But I think interjecting constraints like salary cap, like the fact that you have to choose 2 from each position (1 from center) all reduce the combinations greatly. When you interject even MORE constraints like a MIN salary value for the whole team, it becomes even more interesting.

My question is... how do I do this in excel?

 

[West Man]

The 12 billion combinations includes choosing 1 center and 2 for each of the other positions as well as eliminating any players with a salary of $3700 or less. So that doesn't reduce the total combinations to, in my opinion, a workable number. I worked it by eliminating the lower valued players and then counting (using countif) how many were left for each position. For instance for SG if you have 38 players, the combinations 2 SGs is COMBIN(38,2). The total combinations is simply the product of the combinations for each of the 5 positions.

Introducing a team constraint would require VBA programming, something for which I have very limited skills

 

[Reboshua]

Introducing a team constraint would require VBA programming, something for which I have very limited skills

Thanks again for the reply. Yep, this constraint is by far the most important. I'm not sure eliminating those at $3700 or less is meaningful to the problem as there are plenty of times when you'll roster someone at that price level. The trick is in limiting the total team salary to less than $60,000 for sure, and ideally between $58,000 and $60,000. Once that constraint is introduced, I'm guessing the combos reduce significantly.

 

[West Man]

Well, just for my own amusement, I divided the players by position then randomized their order and wrote a VBA sub to check to see how many teams would fall into the 58,000 to 60,000 range. Running the full combinations might take days, but in testing 20 million combinations I found that 57,205 fell into the test group. So it appears that approximately 0.286% would fall into the range of 58000 - 60000. So you would still have approx.

1.40864E+13 combinations to work with.

<tbody>
</tbody>

 

[Reboshua]

That's incredibly nice of you to do that, because I have no idea how to do it. I grant you the numbers are high when just factoring in salary, but the value here is in introducing other factors like minutes, projected score, etc. All of these things help deliver better accuracy and a lower mean square error for prediction. I just need to learn how to do this myself.