Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fantasy Football & Excel VBA
Hello All,
I have an application for fantasy football for which I'd like to use Excel VBA. First, you have to know a little about the setup. I am joining a FREE auction league, for fun. This league works a little differently than most leagues people here may be involved with. Each player has a certain "cost". Each team owner (me) has a maximum amount he can "spend" on players. Each player is projected to score a certain amount of points for the year (my projections) Each team owner is REQUIRED to have a specific number of players at each position without going over the salary cap. Here are those requirements. Each team owner MUST have: 3 QB's (out of 50 choices) 5 RB's (out of 80 choices) 7 WR's (out of 100 choices) 2 TE's (out of 50 choices) 2 K's (out of 50 choices) 2 Def (out of 50 choices) OK. I have made an excel sheet containing the name, cost, and projected point total of every one of these players. What I'd like to do is cycle through EVERY possible combination setup(yes...I know there would be trillions of combinations) and check the following: What is the total projected point value of the current possible team combination? What is the current cost of the current possible team combination? Subject to the constraints: If projected point value is less than the previously tested projected point value, disregard combination. If the cost of the current combination exceeds my salary cap, disregard combination. In the end, I'm of course looking for the most possible team points (projected) without going over my salary cap. I have a fair amount of excel VBA programming experience and I've thought of these possible hangups regarding the code. How to be efficient? It would be easy to write a code that ended up testing the same combination many times, as you went through testing loops. Given the number of combination, efficiency would be key. How to set up a good looping structure given that positions 1-5 (of the 6 total) would be held stationary while position 6 was cycled and combinations were tested. Then, one slight change would need to be made in position 5 and position 6 would need to be cycled again. This would occur until all of position 5 was cycled and then 1 slght change would be made in position 4, etc.., etc... I realize this will be quite a looping structure. If my math is right, there are trillions and trillions of possible combinations. Is this even practical? Can it work? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fantasy Football & Excel VBA
Do you actually want to list all 380 (by my count) players and their
ratings? It seems to me that you can eliminate before listing all of those that cost more but rate less. "A Mad Doberman" wrote in message oups.com... Hello All, I have an application for fantasy football for which I'd like to use Excel VBA. First, you have to know a little about the setup. I am joining a FREE auction league, for fun. This league works a little differently than most leagues people here may be involved with. Each player has a certain "cost". Each team owner (me) has a maximum amount he can "spend" on players. Each player is projected to score a certain amount of points for the year (my projections) Each team owner is REQUIRED to have a specific number of players at each position without going over the salary cap. Here are those requirements. Each team owner MUST have: 3 QB's (out of 50 choices) 5 RB's (out of 80 choices) 7 WR's (out of 100 choices) 2 TE's (out of 50 choices) 2 K's (out of 50 choices) 2 Def (out of 50 choices) OK. I have made an excel sheet containing the name, cost, and projected point total of every one of these players. What I'd like to do is cycle through EVERY possible combination setup(yes...I know there would be trillions of combinations) and check the following: What is the total projected point value of the current possible team combination? What is the current cost of the current possible team combination? Subject to the constraints: If projected point value is less than the previously tested projected point value, disregard combination. If the cost of the current combination exceeds my salary cap, disregard combination. In the end, I'm of course looking for the most possible team points (projected) without going over my salary cap. I have a fair amount of excel VBA programming experience and I've thought of these possible hangups regarding the code. How to be efficient? It would be easy to write a code that ended up testing the same combination many times, as you went through testing loops. Given the number of combination, efficiency would be key. How to set up a good looping structure given that positions 1-5 (of the 6 total) would be held stationary while position 6 was cycled and combinations were tested. Then, one slight change would need to be made in position 5 and position 6 would need to be cycled again. This would occur until all of position 5 was cycled and then 1 slght change would be made in position 4, etc.., etc... I realize this will be quite a looping structure. If my math is right, there are trillions and trillions of possible combinations. Is this even practical? Can it work? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fantasy Football & Excel VBA
Nahh...I don't want to actually list all the players. I'm really just
trying to get ideas on an algorithm that will cycle through all the combinations and check them. The auction system is set up so there are not very many players who cost more but rate less, as you were saying. There are, however, many tiers. There are many players who rate less, but cost less. So, for instance, you might spend a large part of your salary cap on a QB, but will have to sacrifice on RB and/or WR. That's were the algorithm comes into play. Trying to find the absolute best combination of players (based on my point projections), without going over the salary cap. David Cox wrote: Do you actually want to list all 380 (by my count) players and their ratings? It seems to me that you can eliminate before listing all of those that cost more but rate less. "A Mad Doberman" wrote in message oups.com... Hello All, I have an application for fantasy football for which I'd like to use Excel VBA. First, you have to know a little about the setup. I am joining a FREE auction league, for fun. This league works a little differently than most leagues people here may be involved with. Each player has a certain "cost". Each team owner (me) has a maximum amount he can "spend" on players. Each player is projected to score a certain amount of points for the year (my projections) Each team owner is REQUIRED to have a specific number of players at each position without going over the salary cap. Here are those requirements. Each team owner MUST have: 3 QB's (out of 50 choices) 5 RB's (out of 80 choices) 7 WR's (out of 100 choices) 2 TE's (out of 50 choices) 2 K's (out of 50 choices) 2 Def (out of 50 choices) OK. I have made an excel sheet containing the name, cost, and projected point total of every one of these players. What I'd like to do is cycle through EVERY possible combination setup(yes...I know there would be trillions of combinations) and check the following: What is the total projected point value of the current possible team combination? What is the current cost of the current possible team combination? Subject to the constraints: If projected point value is less than the previously tested projected point value, disregard combination. If the cost of the current combination exceeds my salary cap, disregard combination. In the end, I'm of course looking for the most possible team points (projected) without going over my salary cap. I have a fair amount of excel VBA programming experience and I've thought of these possible hangups regarding the code. How to be efficient? It would be easy to write a code that ended up testing the same combination many times, as you went through testing loops. Given the number of combination, efficiency would be key. How to set up a good looping structure given that positions 1-5 (of the 6 total) would be held stationary while position 6 was cycled and combinations were tested. Then, one slight change would need to be made in position 5 and position 6 would need to be cycled again. This would occur until all of position 5 was cycled and then 1 slght change would be made in position 4, etc.., etc... I realize this will be quite a looping structure. If my math is right, there are trillions and trillions of possible combinations. Is this even practical? Can it work? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fantasy Football & Excel VBA
I have grasped the essence of the problem, and can see commercial
possibilities in a solution. This is the sort of problem that is just up my street,. I have written a commercial VB app that nearly optimises the path of a PCB drilling machine. This made a $70,000 machine operate 15% faster than when programmed by an "expert" programmer. The solution of such problems relies on the method used to cut down the number of unwanted comparisons. I am sure that there are thousands of excel programmers that can write a program to consider all of the possibilities, but you ain't gonna live that long. I am trying to save those trying to help you from writing code that will not have any practical use. So, can we assume that you have eliminated all of the no-hopers? Now I would want to produce columns like so: Position _|_______________QB2____|___ RB5____ |____ WR7____ |etc each position to have at least three columns: estimated points, cost, estimated. points/cost The estimated cost divided by the cost gives a bang per buck figure. I would want this sorted in descending order, so that the calculations try the best prospects first. This may be enough optimisation to make a solution in Excel practical, although I am inclined to doubt it. Anybody in the group got criticisms of this approach to an Excel solution, or better ideas? Meanwhile I am contemplating if my minimum path work can be adapted to this problem, and how many people would be prepared to pay how much to get the best selections from their point estimations. David F. Cox "A Mad Doberman" wrote in message ups.com... Nahh...I don't want to actually list all the players. I'm really just trying to get ideas on an algorithm that will cycle through all the combinations and check them. The auction system is set up so there are not very many players who cost more but rate less, as you were saying. There are, however, many tiers. There are many players who rate less, but cost less. So, for instance, you might spend a large part of your salary cap on a QB, but will have to sacrifice on RB and/or WR. That's were the algorithm comes into play. Trying to find the absolute best combination of players (based on my point projections), without going over the salary cap. David Cox wrote: Do you actually want to list all 380 (by my count) players and their ratings? It seems to me that you can eliminate before listing all of those that cost more but rate less. "A Mad Doberman" wrote in message oups.com... Hello All, I have an application for fantasy football for which I'd like to use Excel VBA. First, you have to know a little about the setup. I am joining a FREE auction league, for fun. This league works a little differently than most leagues people here may be involved with. Each player has a certain "cost". Each team owner (me) has a maximum amount he can "spend" on players. Each player is projected to score a certain amount of points for the year (my projections) Each team owner is REQUIRED to have a specific number of players at each position without going over the salary cap. Here are those requirements. Each team owner MUST have: 3 QB's (out of 50 choices) 5 RB's (out of 80 choices) 7 WR's (out of 100 choices) 2 TE's (out of 50 choices) 2 K's (out of 50 choices) 2 Def (out of 50 choices) OK. I have made an excel sheet containing the name, cost, and projected point total of every one of these players. What I'd like to do is cycle through EVERY possible combination setup(yes...I know there would be trillions of combinations) and check the following: What is the total projected point value of the current possible team combination? What is the current cost of the current possible team combination? Subject to the constraints: If projected point value is less than the previously tested projected point value, disregard combination. If the cost of the current combination exceeds my salary cap, disregard combination. In the end, I'm of course looking for the most possible team points (projected) without going over my salary cap. I have a fair amount of excel VBA programming experience and I've thought of these possible hangups regarding the code. How to be efficient? It would be easy to write a code that ended up testing the same combination many times, as you went through testing loops. Given the number of combination, efficiency would be key. How to set up a good looping structure given that positions 1-5 (of the 6 total) would be held stationary while position 6 was cycled and combinations were tested. Then, one slight change would need to be made in position 5 and position 6 would need to be cycled again. This would occur until all of position 5 was cycled and then 1 slght change would be made in position 4, etc.., etc... I realize this will be quite a looping structure. If my math is right, there are trillions and trillions of possible combinations. Is this even practical? Can it work? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fantasy Football & Excel VBA
The brain ticks on.
Arranging the data as I have suggested leaves three possibilities. 1) The players top of the bang-per-bug ratings total cost is equal to the budget - problem solved. 2) The total cost exceeds the budget. In this case you have to try swapping players for those with lower costs. 3) The total cost is lower than the budget. In this case you have to try swapping players for those with more points. HTH "David Cox" wrote in message ... I have grasped the essence of the problem, and can see commercial possibilities in a solution. This is the sort of problem that is just up my street,. I have written a commercial VB app that nearly optimises the path of a PCB drilling machine. This made a $70,000 machine operate 15% faster than when programmed by an "expert" programmer. The solution of such problems relies on the method used to cut down the number of unwanted comparisons. I am sure that there are thousands of excel programmers that can write a program to consider all of the possibilities, but you ain't gonna live that long. I am trying to save those trying to help you from writing code that will not have any practical use. So, can we assume that you have eliminated all of the no-hopers? Now I would want to produce columns like so: Position _|_______________QB2____|___ RB5____ |____ WR7____ |etc each position to have at least three columns: estimated points, cost, estimated. points/cost The estimated cost divided by the cost gives a bang per buck figure. I would want this sorted in descending order, so that the calculations try the best prospects first. This may be enough optimisation to make a solution in Excel practical, although I am inclined to doubt it. Anybody in the group got criticisms of this approach to an Excel solution, or better ideas? Meanwhile I am contemplating if my minimum path work can be adapted to this problem, and how many people would be prepared to pay how much to get the best selections from their point estimations. David F. Cox "A Mad Doberman" wrote in message ups.com... Nahh...I don't want to actually list all the players. I'm really just trying to get ideas on an algorithm that will cycle through all the combinations and check them. The auction system is set up so there are not very many players who cost more but rate less, as you were saying. There are, however, many tiers. There are many players who rate less, but cost less. So, for instance, you might spend a large part of your salary cap on a QB, but will have to sacrifice on RB and/or WR. That's were the algorithm comes into play. Trying to find the absolute best combination of players (based on my point projections), without going over the salary cap. David Cox wrote: Do you actually want to list all 380 (by my count) players and their ratings? It seems to me that you can eliminate before listing all of those that cost more but rate less. "A Mad Doberman" wrote in message oups.com... Hello All, I have an application for fantasy football for which I'd like to use Excel VBA. First, you have to know a little about the setup. I am joining a FREE auction league, for fun. This league works a little differently than most leagues people here may be involved with. Each player has a certain "cost". Each team owner (me) has a maximum amount he can "spend" on players. Each player is projected to score a certain amount of points for the year (my projections) Each team owner is REQUIRED to have a specific number of players at each position without going over the salary cap. Here are those requirements. Each team owner MUST have: 3 QB's (out of 50 choices) 5 RB's (out of 80 choices) 7 WR's (out of 100 choices) 2 TE's (out of 50 choices) 2 K's (out of 50 choices) 2 Def (out of 50 choices) OK. I have made an excel sheet containing the name, cost, and projected point total of every one of these players. What I'd like to do is cycle through EVERY possible combination setup(yes...I know there would be trillions of combinations) and check the following: What is the total projected point value of the current possible team combination? What is the current cost of the current possible team combination? Subject to the constraints: If projected point value is less than the previously tested projected point value, disregard combination. If the cost of the current combination exceeds my salary cap, disregard combination. In the end, I'm of course looking for the most possible team points (projected) without going over my salary cap. I have a fair amount of excel VBA programming experience and I've thought of these possible hangups regarding the code. How to be efficient? It would be easy to write a code that ended up testing the same combination many times, as you went through testing loops. Given the number of combination, efficiency would be key. How to set up a good looping structure given that positions 1-5 (of the 6 total) would be held stationary while position 6 was cycled and combinations were tested. Then, one slight change would need to be made in position 5 and position 6 would need to be cycled again. This would occur until all of position 5 was cycled and then 1 slght change would be made in position 4, etc.., etc... I realize this will be quite a looping structure. If my math is right, there are trillions and trillions of possible combinations. Is this even practical? Can it work? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fantasy Football & Excel VBA
http://office.microsoft.com/en-ca/as...246031033.aspx
"A Mad Doberman" wrote in message oups.com... Hello All, I have an application for fantasy football for which I'd like to use Excel VBA. First, you have to know a little about the setup. I am joining a FREE auction league, for fun. This league works a little differently than most leagues people here may be involved with. Each player has a certain "cost". Each team owner (me) has a maximum amount he can "spend" on players. Each player is projected to score a certain amount of points for the year (my projections) Each team owner is REQUIRED to have a specific number of players at each position without going over the salary cap. Here are those requirements. Each team owner MUST have: 3 QB's (out of 50 choices) 5 RB's (out of 80 choices) 7 WR's (out of 100 choices) 2 TE's (out of 50 choices) 2 K's (out of 50 choices) 2 Def (out of 50 choices) OK. I have made an excel sheet containing the name, cost, and projected point total of every one of these players. What I'd like to do is cycle through EVERY possible combination setup(yes...I know there would be trillions of combinations) and check the following: What is the total projected point value of the current possible team combination? What is the current cost of the current possible team combination? Subject to the constraints: If projected point value is less than the previously tested projected point value, disregard combination. If the cost of the current combination exceeds my salary cap, disregard combination. In the end, I'm of course looking for the most possible team points (projected) without going over my salary cap. I have a fair amount of excel VBA programming experience and I've thought of these possible hangups regarding the code. How to be efficient? It would be easy to write a code that ended up testing the same combination many times, as you went through testing loops. Given the number of combination, efficiency would be key. How to set up a good looping structure given that positions 1-5 (of the 6 total) would be held stationary while position 6 was cycled and combinations were tested. Then, one slight change would need to be made in position 5 and position 6 would need to be cycled again. This would occur until all of position 5 was cycled and then 1 slght change would be made in position 4, etc.., etc... I realize this will be quite a looping structure. If my math is right, there are trillions and trillions of possible combinations. Is this even practical? Can it work? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fantasy Football & Excel VBA
That article is quite interesting. Thanks, David.
David Cox wrote: http://office.microsoft.com/en-ca/as...246031033.aspx "A Mad Doberman" wrote in message oups.com... Hello All, I have an application for fantasy football for which I'd like to use Excel VBA. First, you have to know a little about the setup. I am joining a FREE auction league, for fun. This league works a little differently than most leagues people here may be involved with. Each player has a certain "cost". Each team owner (me) has a maximum amount he can "spend" on players. Each player is projected to score a certain amount of points for the year (my projections) Each team owner is REQUIRED to have a specific number of players at each position without going over the salary cap. Here are those requirements. Each team owner MUST have: 3 QB's (out of 50 choices) 5 RB's (out of 80 choices) 7 WR's (out of 100 choices) 2 TE's (out of 50 choices) 2 K's (out of 50 choices) 2 Def (out of 50 choices) OK. I have made an excel sheet containing the name, cost, and projected point total of every one of these players. What I'd like to do is cycle through EVERY possible combination setup(yes...I know there would be trillions of combinations) and check the following: What is the total projected point value of the current possible team combination? What is the current cost of the current possible team combination? Subject to the constraints: If projected point value is less than the previously tested projected point value, disregard combination. If the cost of the current combination exceeds my salary cap, disregard combination. In the end, I'm of course looking for the most possible team points (projected) without going over my salary cap. I have a fair amount of excel VBA programming experience and I've thought of these possible hangups regarding the code. How to be efficient? It would be easy to write a code that ended up testing the same combination many times, as you went through testing loops. Given the number of combination, efficiency would be key. How to set up a good looping structure given that positions 1-5 (of the 6 total) would be held stationary while position 6 was cycled and combinations were tested. Then, one slight change would need to be made in position 5 and position 6 would need to be cycled again. This would occur until all of position 5 was cycled and then 1 slght change would be made in position 4, etc.., etc... I realize this will be quite a looping structure. If my math is right, there are trillions and trillions of possible combinations. Is this even practical? Can it work? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help me win my fantasy football league by answering this excel ? | Excel Worksheet Functions | |||
Fantasy Football Conditional Arrays | Excel Worksheet Functions | |||
Fantasy Football | Excel Discussion (Misc queries) | |||
Fantasy Football | Excel Discussion (Misc queries) | |||
Fantasy Football | New Users to Excel |