# Excel Optimization with Fantasy Football Stat

August 26, 2010 at 18:32:46
Specs: Windows 7, i5 750 / 8GBs
 Hi,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 26RB  Chris Johnson 307.8 40RB  Adrian Peterson 300.7 38RB  Maurice Jones 295.2 37RB  Ray Rice 283.2 37RB  Frank Gore 247 34WR  Andre Johnson 314.5 32WR  Reggie Wayne 279.5 28WR  Miles Austin 272.5 27WR  Randy Moss 268.8 29WR  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 playerI 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 \$ PlayerQB 424.40 29  Aaron RodgersRB 307.80 40  Chris JohnsonRB 307.80 40  Chris JohnsonWR 314.50 32  Andre JohnsonWR 314.50 32  Andre JohnsonWR 314.50 32  Andre JohnsonFlex 314.50 32  Andre JohnsonTE 259.70 21  Jermichael Finley K 133.00 6  Stephen GostkowskiDEF 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

#1
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 6 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 •

#2
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 29Im 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, 1DEF18 players on your team at minimum , 30 players maximum\$250 auction dollars to spend on all your playersI 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 formattingI 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