Solver Add-In Help

Darkmanz

New Member
Joined
Aug 8, 2013
Messages
18
This may be more complex than what this forum is intended for, but I will state my desired goal and see if someone is willing to help me through it.
I am attempting to create a Fantasy Football Draft assistance worksheet for my personal use. We have a very unusual setup with our league, and to my knowledge there is nothing existing out there that will fit our constraints. Here is a rundown of the rules(that pertain to this worksheet).

1. Number of Teams in the League varies between 8-12 each year depending on participants
2. We select 60 players to "Auction Draft" (usually top 20 at each position QB,RB,WR but we also toss in a couple of TE deducting from one of the other positions to keep the total balance at 60)
3.In the Auction each team has $200 to bid on players
4.We have a 16 player roster
5.Starters each week is 1xQB/2xRB/2xWR/1xTE/1xK/1xD/ and 1x FLEX (QB,RB,WR,TE) (essentially it is a 2 QB league as you almost always would start a QB in the Flex based on the way the points roll out in our league)
6.Once all 60 players are "Auction drafted" we begin a "Snake Draft" to fill out the remainder of our Rosters (Draft Order is determined by $ remaining from the Auction)

I think that gives the basic layout needed to understand my question, so here goes. I am looking for help with the "Auction part of the Draft and Dynamically assigning $ values to players based on a few things. I have already worked up a "Points Value" for each player and a curve set associated with it. What I need to be able to do is translate that number to a $ amount that takes into account That each team only has $200 so if a player goes for more than the value I have assigned to them the others below Dynamically adjust or vise versa. I would also like it to be able to take into account my team needs. i.e. if I already have 1 QB it does not weight QB as heavily for the remainder.

It seems I cannot Attach files to the forums so I will try to make a quick and dirty example with the tools for the forum.

Any help would be greatly appreciated!! Thanks folks

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 404"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Order[/TD]
[TD]Name/Team/Position[/TD]
[TD]Projected Value[/TD]
[TD]Estimated $ value
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Manning, Peyton (Den) QB[/TD]
[TD]461[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rodgers, Aaron (GB) QB
[/TD]
[TD]425[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Peterson, Adrian (Min) RB[/TD]
[TD]317[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Brees, Drew (NO) QB[/TD]
[TD]421[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Luck, Andrew (Ind) QB[/TD]
[TD]365[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Brady, Tom (NE) QB[/TD]
[TD]345[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Charles, Jamaal (KC) RB[/TD]
[TD]305[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Newton, Cam (Car) QB[/TD]
[TD]367[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]McCoy, LeSean (Phi) RB[/TD]
[TD]308[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Stafford, Matthew (Det) QB[/TD]
[TD]369[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Griffin, Robert (Wsh) QB[/TD]
[TD]363[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Lynch, Marshawn (Sea) RB[/TD]
[TD]295[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Wilson, Russell (Sea) QB[/TD]
[TD]338[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Romo, Tony (Dal) QB[/TD]
[TD]350[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Johnson, Calvin (Det) WR[/TD]
[TD]254[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Kaepernick, Colin (SF) QB[/TD]
[TD]335[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Foles, Nick (Phi) QB[/TD]
[TD]350[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Ryan, Matt (Atl) QB[/TD]
[TD]348[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Forte, Matt (Chi) RB[/TD]
[TD]303[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Martin, Doug (TB) RB[/TD]
[TD]267[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Thomas, Demaryius (Den) WR[/TD]
[TD]234[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Stacy, Zac (StL) RB[/TD]
[TD]243[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Foster, Arian (Hou) RB[/TD]
[TD]234[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Bryant, Dez (Dal) WR[/TD]
[TD]220[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Green, A.J. (Cin) WR[/TD]
[TD]217[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Lacy, Eddie (GB) RB[/TD]
[TD]279[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Murray, DeMarco (Dal) RB[/TD]
[TD]242[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]Bell, Le'Veon (Pit) RB[/TD]
[TD]285[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]Morris, Alfred (Wsh) RB[/TD]
[TD]236[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Rivers, Philip (SD) QB[/TD]
[TD]338[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]Cutler, Jay (Chi) QB[/TD]
[TD]311[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]Graham, Jimmy (NO) TE[/TD]
[TD]205[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Roethlisberger, Ben (Pit) QB[/TD]
[TD]303[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Dalton, Andy (Cin) QB[/TD]
[TD]310[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Marshall, Brandon (Chi) WR[/TD]
[TD]214[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]Jones, Julio (Atl) WR[/TD]
[TD]210[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]Nelson, Jordy (GB) WR[/TD]
[TD]204[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]Bush, Reggie (Det) RB[/TD]
[TD]224[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]Jeffery, Alshon (Chi) WR[/TD]
[TD]207[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]Brown, Antonio (Pit) WR[/TD]
[TD]209[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]Johnson, Andre (Hou) WR[/TD]
[TD]198[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]Thomas, Julius (Den) TE[/TD]
[TD]178[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]Jackson, Vincent (TB) WR[/TD]
[TD]186[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]Manning, Eli (NYG) QB[/TD]
[TD]294[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]Cobb, Randall (GB) WR[/TD]
[TD]202[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]Fitzgerald, Larry (Ari) WR[/TD]
[TD]171[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]Garcon, Pierre (Wsh) WR[/TD]
[TD]166[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]Jackson, DeSean (Wsh) WR[/TD]
[TD]160[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]Allen, Keenan (SD) WR[/TD]
[TD]167[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]Ball, Montee (Den) RB[/TD]
[TD]232[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]Bernard, Giovani (Cin) RB[/TD]
[TD]229[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]Cruz, Victor (NYG) WR[/TD]
[TD]160[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]Flacco, Joe (Bal) QB[/TD]
[TD]284[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD]Mathews, Ryan (SD) RB[/TD]
[TD]212[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD]Spiller, C.J. (Buf) RB[/TD]
[TD]212[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]Gore, Frank (SF) RB[/TD]
[TD]223[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]Rice, Ray (Bal) RB[/TD]
[TD]214[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]58[/TD]
[TD]Hilton, T.Y. (Ind) WR[/TD]
[TD]154[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]59[/TD]
[TD]Palmer, Carson (Ari) QB[/TD]
[TD]293[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Welker, Wes (Den) WR[/TD]
[TD]148
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 128"]
<colgroup><col style="width:48pt" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Team 1[/TD]
[TD="class: xl65, width: 64, align: right"]$200[/TD]
[/TR]
[TR]
[TD]Team 2[/TD]
[TD="class: xl65, align: right"]$200[/TD]
[/TR]
[TR]
[TD]Team 3[/TD]
[TD="class: xl65, align: right"]$200[/TD]
[/TR]
[TR]
[TD]Team 4[/TD]
[TD="class: xl65, align: right"]$200[/TD]
[/TR]
[TR]
[TD]Team 5[/TD]
[TD="class: xl65, align: right"]$200[/TD]
[/TR]
[TR]
[TD]Team 6[/TD]
[TD="class: xl65, align: right"]$200[/TD]
[/TR]
[TR]
[TD]Team 7[/TD]
[TD="class: xl65, align: right"]$200[/TD]
[/TR]
[TR]
[TD]Team 8[/TD]
[TD="class: xl65, align: right"]$200[/TD]
[/TR]
[TR]
[TD]Team 9[/TD]
[TD="class: xl65, align: right"]$200[/TD]
[/TR]
[TR]
[TD]Team 10[/TD]
[TD="class: xl65, align: right"]$200[/TD]
[/TR]
[TR]
[TD="class: xl66"]Total:[/TD]
[TD="class: xl67, align: right"]$2,000[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top