So I have a spreadsheet that I’m using for my fantasy football auction draft. The spreadsheet contains the positions (QB1, QB2, RB1,…Bench, Bench) in one column and the number of dollars spent in the column directly to the right of the position. I have a total number of dollars spent, which is just the sum of the dollars spent column. I also have dollars left, which is just our budget ($200) minus the total dollars spent thus far. Now, you need to be able to spend at least $1 on each position (i.e. you can not leave any positions undrafted). Therefore, I created a simple formula that will show me the number of dollars left in the budget per position yet to be drafted. Thats the simple part. The hard part is figuring out how much you can spend on your next pick without going under the $1 left per position not drafted. I can easily use a goal seek for this, but I need the goal seek to update dynamically as players are drafted. Any ideas how to do this?
Seems like you should be able to do something with the countif() function
I am using a count function to figure out the budget left per undrafted position. The formula is dollars left (budget - sum of dollars paid thus far) divided by 15-count(# of positions drafted). 15 is the total number of positions that have to be drafted. However, to figure out how much you can spend on the next player without going under budget (under $1 left per undrafted player) you need to use a goal seek. And that goal seek number will change every time you draft another player.
I just did this in excel using a column of each position (Q,Q,RB,RB,RB,WR,WR,WR) and amount spent. A column of total spent per position(Q,RB,WR) using SumIf to give me the $ spent per position. And the function countif(summary_range,0) to give me the number of positions that haven’t spent a dollar yet. So I reduce 200 by total spent (sum first group) and by the result of my countif function. Does this make sense ?
Yep, that works. Thanks.
------------------------ www.ozgrid.com You’ll need to search other posts to get your answer. Very likely that someone asked the same question before. You’ll need to register in order to post, but you can view posts without the need to register. ------------------------ http://groups.google.co.uk/group/microsoft.public.excel/topics?lnk=srg You’ll just ask your question and get an answer in probably 5-10 minutes if not less. The only con is that you have to register for Google Groups to be able to post…, though you can view without the need to register. ------------------------ http://www.cpearson.com/excel/topic.aspx ------------------------ http://www.contextures.com/tiptech.html ------------------------
Another excel question: I’m starting with a column of data that always contains “PS” in the middle, like this: variousPSother And I want to end up with two cells that are split right before “PS”, like this: various PSother Does anyone know the formula for this? I think it uses the LEN function.
Use text to columns under “data”
How do you do this using text to columns? I can only type in one letter in the “other” field, which means I can only separate by “P”, not by “PS”. king_kong Wrote: ------------------------------------------------------- > Use text to columns under “data”
Ahh, ok, I figured it out, here are the formulas: =LEFT(A5,(FIND(“PS”,A5)-1)) =RIGHT(A5,(LEN(A5)-FIND(“PS”,A5)-3))