This has me stumped

[JCarmack]

Is this even possible:

I have exported a fantasy football cheatsheet from the internet and want to see if I can do the following.

In column A would be the list quarterbacks
In column B would be their estimated fantasy points per week
In column C would be their cost (based on a $200 salary cap)

In column D, E, and F would have the same (name, estimated points per week, and cost); D,E, and F would be for the running back position

In columns G, H, and I would have the same three thing but for wide receivers

Columns J,K,and L would be for Tight Ends

Columns M,N,and O would be for kickers

Columns P,Q,and R would be for team defenses

****Now what I aim trying to do is get excel to choose the optimum team (one that makes up the most points based on certain restrictions).

***I need excel to choose ONLY 1 QB, 2 RB, 3 WR, 1 TE, 1 K, and 1 D with the restrictions that the total salary does not exceed a certain number.

I just basically want excel to identify the highest scoring team based on column B, E, H, K, N, and Q. I need it to stay in the confines of choosing only 1 QB, 2 RB, 3 WR, 1 TE, 1 K, and 1 D while also staying under a predetermined total price.

Hope this made some sense...confusing I know but I wonder if its possible

All help is greatly appreciated

 

[JCarmack]

bump

 

[Ruddles]

If I've understood you correctly, that's only six different combinations, isn't it?

 

[moonfish]

Depending on the number of possibilities, this can be solved with excel's LP solver.
EDIT: if there are more than 200 available players to choose from, skip everything below and get someone to write a more capable LP solver for you

http://www.wikihow.com/Use-Solver-in-Microsoft-Excel

Just make sure that you you organize your data so that all your players (regardless of their position) are all in the same column, with all their attributes below in numerical values.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	*	player1	player4	player5	player6	player9	player10	etc	*	*	*	*
2	*	*	*	*	*	*	*	*	*	LHS	*	RHS
3	is QB	1	*	*	*	*	*	*	*	0	=	1
4	is RB	*	1	*	*	*	*	*	*	0	=	2
5	is WR	*	*	1	1	*	*	*	*	0	=	3
6	is TE	*	*	*	*	1	*	*	*	0	=	1
7	is K	*	*	*	*	*	1	*	*	0	=	1
8	is D	*	*	*	*	*	*	1	1	0	=	1
9	cost	500	100	300	200	200	100	300	500	0		1500
Sheet1

The tutorial should be able to help you with using the solver but if you have any questions about sorting your data and setting up your conditions, feel free to ask.

 

[JCarmack]

thanks...still a little confused though...anyone else...Id greatly appreciate it

 

[JCarmack]

bump

 

[moonfish]

I'll help you get started if you provide an example from your data.

What I'd like to see is this:
- Full stats for 1 QB, 2 RB, 3 WR, 1 TE, 1 K and 1 D (so basically, one whole team);
- The score for this team and how it is calculated;
- full stats for 1 QB, 1 RB, 1 WR, 1 TE, 1 K and 1 D (DIFFERENT from the ones in the first team).

Full stats means any attribute you can think of, including cost.

 

[shg]

I think there are on the order of 250 players to select among, each of whom would be drafted or not, which would exceed the Excel Solver's ability.

My approach would be to populate the team with the required number of positions, selecting in each case the players with the highest number of points per unit salary. That would exceed the salary cap, so then do a backtrack search, starting with the drafted player with the highest salary, substituting the next best player at that position (points per unit salary)with a lower salary. I don't know that that is an optimum algorithm, but it sounds like a start.

It would be a non-trivial piece of programming (for me, anyway).

 

[shg]

From http://fantasynews.cbssports.com/fantasyfootball/draft/averages, there are this many players at each position:

      -I- ---J--- --K--- ---L---
  3   Pos Choices Choose Combos 
  4   QB       30      1     30 
  5   RB       67      2  2,211 
  6   WR       75      3 67,525 
  7   TE       19      1     19 
  8   K        26      1     26 
  9   D        19      1     19

That's about 42 trillion combinations to evaluate if you were doing it by brute force.

 

[moonfish]

If anyone can tell me how the strength of a team is calculated I can use this as a chance to polish up on my GA.

I'm clueless when it comes to football, so be specific :P