Maximum Spendature Based Upon Budget and Empty Cells for Fantasy Football

[nickmb17]

Hello, I am creating a fantasy football draft board using excel. To keep it short and sweet, each individual team (one per column) has a $200 budget to draft players. Each team must fill 15 roster positions (one per row). Here's the catch; you must spend a minimum of $1 on all of the 15 players on your roster. As a result of this, The cell/formula I am having trouble creating is a "maximum bid" amount which is your remaining budget subtracted by $1 per every remaining roster position/row you have left to fill.

To provide some examples, before any roster positions (rows) have been filled, the team is only allowed to spend a maximum amount $186 out of their $200 budget, as they would need to spend at least $1 on all of the remaining 14 positions. Let's say the team has 10 positions filled and has spent $150, their maximum bid would then be $45 ($50-$5($1 minimum for each of the remaining blank cells)). On the other end of the example spectrum, if a team has spent $1 each on filling 14 roster positions, their remaining maximum bid on the final and 15th roster position would be $186.

Essentially, this "maximum bid" cell must display $200, subtracted by the sum of all values manually inserted into rows, also subtracted by an additional $1 for every blank cell still remaining.Attachment 401928


Let me know if any clarification is needed and thanks for your help!

[x65140]

Nick - -
Here is a broken down version of what I used last year.
This is set up for 2014, so you'll have to make changes.
MESSAGE ME FOR MORE INFORMATION!