Excel Optimization with Fantasy Football Stat

August 26, 2010 at 18:32:46
Specs: Windows 7, i5 750 / 8GBs

So im in an auction based fantasy draft in which we have $250 to spend on players with set dollar values. Everyone can take the same players it doesnt matter - so the contest is really who optimizes there selection based on projected fantasy points.

Anyway this is what i have in excel so far:

Heres a very brief example of what im trying to do:

Say you have a list of players by position, with there project fantasy points and auction dollar value (how much of your $250 salary cap they cost)...

Player FFPts $
QB Aaron Rodgers 424.4 29
 QB Drew Brees 418.4 28
 QB Tom Brady 392.5 24
 QB Peyton Manning 389.2 28
 QB Tony Romo 370.4 26
RB  Chris Johnson 307.8 40
RB  Adrian Peterson 300.7 38
RB  Maurice Jones 295.2 37
RB  Ray Rice 283.2 37
RB  Frank Gore 247 34
WR  Andre Johnson 314.5 32
WR  Reggie Wayne 279.5 28
WR  Miles Austin 272.5 27
WR  Randy Moss 268.8 29
WR  Roddy White 264.3 27


I can start 1 qb, 2 rbs, 3 wrs, and 1 flex (te, wr, rb)

I want excel to optimize my lineup with the most fantasy point potential per my $250 cap. However i cannot figure out how to make it so excel doesnt pick the same player twice since you cannot have duplicates of the same player

I put maximum functions in the FF PTS column point to my project stats (and index, match functions to pull the FF PTS corresponding player and cost.

FF PTS $ Player
QB 424.40 29  Aaron Rodgers
RB 307.80 40  Chris Johnson
RB 307.80 40  Chris Johnson
WR 314.50 32  Andre Johnson
WR 314.50 32  Andre Johnson
WR 314.50 32  Andre Johnson
Flex 314.50 32  Andre Johnson
TE 259.70 21  Jermichael Finley 
K 133.00 6  Stephen Gostkowski
DEF 146.00 6  Green Bay Packers

TOTAL 2836.70 270.00

Say the first RB slot pulls the player with the maximum ffpts - how can i make the second RB cell to find the 2ND BEST MAX relative to the maximum of the first RB?

I appreciate any help - this is been driving me crazy!

See More: Excel Optimization with Fantasy Football Stat

August 26, 2010 at 19:23:42
re: "how can i make the second RB cell to find the 2ND BEST MAX relative to the maximum of the first RB?

I don't know how your worksheet is set up, so I'll just toss out this example and let you deal with the cell references.

The Array Formulas in B7 and B8 will return the 1st and 2st highest values from C1:C5, respectively.

To create an Array Formula, you need to use Ctrl-Shift-Enter and Excel will add the {} around the formula. Do not type them in.

Each time you edit the formula, you need to use Ctrl-Shift-Enter again.

    A       B              C          D
1   RB  Chris Johnson     307.8       40
2   RB  Adrian Peterson   300.7       38
3   RB  Maurice Jones     295.2       37
4   RB  Ray Rice          283.2       37
5   RB  Frank Gore        247         34
7   RB  {=LARGE(IF($A$1:$A$5=A7,$C$1:$C$5),1)}
8   RB  {=LARGE(IF($A$1:$A$5=A7,$C$1:$C$5),2)}

Report •

August 29, 2010 at 13:39:53
Thanks for getting back to me DerbyDad - that worked as far as choosing the player with the most projected points in order. However I have a list of 300+ players that are organized in my spreadsheet as follows: position, name, ffpts, and Auction value $ per player (out of the $250)

ex: Position Player FFPts $
QB  Aaron Rodgers 424.4 29

Im trying to have solver go through this list, and based on my contraints, optimize a lineup that with maximum projected fantasy points as the target cell.

My constraints are as follows:

10 starters (1QB, 2RB, 3WR, 1 TE, 1 Flex (1RB or 1WR or 1TE), 1K, 1DEF

18 players on your team at minimum , 30 players maximum

$250 auction dollars to spend on all your players

I tried running solver - using the sum of the ffpts extracted by your LARGE functions as the target cell. Constraints would be as previously mentioned. But not sure what to have solver change - everytime i select the cells where the large functions are solver reports an error and the cell contents convert into VALUE formatting

I hope this clears up what im trying to do - i wish i could add my spread sheet as an attachment. If theres a way for me to do that (or send a screenshot) I can send you a macro disabled version

thanks again

Report •
Related Solutions

Ask Question