Trouble with Conditional formatting.

BFFLA.xlsx

I did mean Sum(B24:B40)>300. Note that when a player for a team is entered he must be purchased at a cost. That cost can not be so much as to not allow enough money to purchase the remaining 17 players. Example if I have to spend no more than $300 on all 18 players, the most you can spend on any 1 player is $283, leaving $1 for each additional player. You must have at least $1 per player. The total salary cap is $300 for a team.

Long story short, my algorithm works pretty good except for what is entered in spot #18 (the final spot). I can not get the algorithm to understand that if the total salary cap exceeds $300 (due to what gets manually entered in the #18 spot, the value should read "Not Enough Money to Complete Transaction". If you enter values into the cells (B24 through B40), you will see how it works. I used an if/and statement and it seems to limit the output of the final cell. To see the outcome of my problem, enter $283 in cell B24 and enter $1 in the remainder of the cells. Then change cell B40 to 3 and watch the results. Now change it back to 1. Then change cell B41 to 3. Notice it does not have the same behavior. Thanks for any help in advance!

Using this example above, when $1 is entered into B41, the Total purse remaining show $0.00. that is perfect. When X>1, there is a negative output. I want to not allow a negative output. i want it to say "Not Enough Money to Complete Transaction" once that value causes the algorithm to exceed $300.

Before any of my narritive there is an attachment titled BFFLA.xlsx. It is hyperlinked. Thanks again for any help. Let me know if you are having trouble seeing it.

That seems to me an overly complicated approach to a fairly simple problem. The maximum amount that can be entered into any one of the cells is the cap amount minus the number of players in a team less one, i.e. 300 - 17

You can replace the formula at the top of your columns with

=IF(OR(MAX(B24:B41)>($D$8-17),SUM(B24:B41)>$D$8),"Not enough money in account",$D$8-SUM(B24:B41))

Or, you could use data validation with a custom formula for the data entry cells.

=AND(MAX(B$24:B$41)<=($D$8-17),SUM(B$24:B$41)<=$D$8)

With data validation in place, you just need a simple sum for the "Total purse remaining", since the user will not be able to exceed the purse amount.

See attached for both versions in place. Team 1 has the warning with the formula at the top. Team 2 has the data validation and a simple sum.

I looked at the max function initially but I had the same problem you have. For an example enter 283 Dollars in the first cell (B24). Because there are severnteen players left to buy and you only have $17 left, you can only spend $1 for every other player without exceeding your limit. Now, also enter $2 in cell B25 as well as the $283 in cell B24. That should output TRUE, hence stating "Not enough money". Do you see a way to incorporate a small formula as opposed to `17 other logicals in the OR statement, to make this work for each position.

Basically you have to have 18 players and you have to at least spend $1 on every player. If that aguement is false it would yeild "Not enough......" Even if you get to the very last player and you have $2 left, I want to make sure you can not enter a value larger than $2. Neither of these seem to address that situation. Thanks for the help.

change the formula at the top of the column to

=IF(OR(MAX(B24:B41)>($D$8-17),SUM(B24:B41)>$D$8,18-COUNT(B24:B41)>$D$8-SUM(B24:B41)),"Not enough money in account",$D$8-SUM(B24:B41))

This checks if the number of empty cells is bigger than the dollar amount left to spend.

If you want to use the data validation approach, change the data validation custom formula to

=AND(MAX(B$24:B$41)<=($D$8-17),SUM(B$24:B$41)<=$D$8,18-COUNT(B$24:B$41)<=$D$8-SUM(B$24:B$41))

see attached