Trending and value projections

[Darkmanz]

Hi all,

Let me start off by saying that this is a hobby project for a Fantasy Football League and the scope of the question may be beyond what anyone here is willing to help provide but I have tried several approaches to solve my problem and I cannot seem to get my head around it so any help is greatly appreciated.
Our Method of selecting players is fairly unorthodox in comparison to the normal leagues and without getting too deep into the weeds we use a combination of an "Auction" and "Snake Draft" system. The important part for my question is the "Auction" portion of the Draft.
There are 12 teams each with $200 to spend and a list of 72 NFL Players to bid on. Each NFL player has a value based on an anticipated(projected) performance for the upcoming year.

Example:
PlayerA= Expected to produce 400 Fantasy Points at a value of $100
PlayerB= Expected to produce 315 Fantasy Points at a value of $65
PlayerC= Expected to produce 350 Fantasy Points at a value of $80
....and so on

What I am trying to do is set the value of each player so that the sum of all of the values is equal to the total money in the pool (12 teams at $200 each = $2400) and that when a player is sold in the auction the rest of the values adjust according to what the player actually sold for. Using the above example if Player A was projected to sell for $100 but actually sold for $90 then there is $10 more in the pool to distribute to the projected values of the rest of the players.
I hope this all makes sense to someone, or that someone can get me pointed in the right direction.

Thanks

 

[Darkmanz]

Any Chance anyone is willing to help with this?

 

[shg]

if players are sold at auction with no reserve value, what difference do the posted prices make?

 

[Darkmanz]

From previous experience (We have been doing this style of draft for about 5 years now) I know that players towards the top of the list sell for an inflated price while players at the bottom usually go for less than expected as people are running out of money to spend on them. I have compiled the draft data and have established a curve that can closely predict the cost of a player as we move through the auction. The problem I have is that my projected value is a fixed number and if a trend starts somewhere of either overspending or underspending then I just mentally have to do some adjusting on the fly to compensate. If I can base the values from the total pool of Money available and have them dynamically change based on the money spent I can more accurately predict the worth of each player as we are drafting and thus decide where my spending cap for each player should be.

I hope that answers the question better.

Thanks

 

[shg]

Row\Col	A​	B​	C​	D​	E​
1​	Plaver​	Price​	Sold​	Reprice​	​
2​	Hank	100​	95​		D2: =IF(ISNUMBER(C2), "", B2 * $C$24 / $C$25)
3​	Quin	100​		102.73​
4​	Cain	95​		97.60​
5​	Drew	85​		87.32​
6​	Otto	80​	80​
7​	Kent	75​		77.05​
8​	John	70​		71.91​
9​	Paul	70​		71.91​
10​	Toby	70​		71.91​
11​	Ivan	65​		66.78​
12​	Gary	60​	40​
13​	Seth	60​		61.64​
14​	Abel	50​		51.37​
15​	Eric	50​		51.37​
16​	Mark	40​		41.09​
17​	Rick	40​		41.09​
18​	Leon	30​		30.82​
19​	Bill	5​		5.14​
20​	Fred	5​		5.14​
21​	Norm	5​		5.14​
22​
23​	Original​	1155​			B23: =SUM(B2:B21)
24​	​	Undrafted​	940​		C24: =B23 - SUM(C2:C21)
25​		Revalue​	915​		C25: =SUMIF(C2:C21, "", B2:B21)

 

[Darkmanz]

Thank you for your time. This is exactly what I was trying to work out.

 

[shg]

You're welcome.