Excel Formula for Fantasy Football

Hi everyone,

I just joined the board and I am looking for some help creating a formula for a spreadsheet that I created for my fantasy football league.

To keep it simple, I am in an auction style draft where each team bids on the players they would like to have on their team. Bids are in increments of $1, using whole numbers only. Each team has $200 to spend in order to fill their roster, and the minimum bid for every player is $1.

To find the maximum bid, you have to start by figuring out how many open spots there are on their roster. Since every player must cost $1, you assign a $1 value to every open position except for one. For example, if they had 11 open spots, 10 of them have to be worth $1. The 11th spot should equal their remaining money minus the $10 needed to fill out the rest of the roster, or in other words the maximum they can spend on one single player. I have attached my excel spreadsheet in the zip file below. Any help would be greatly appreciated. Thanks!