1. #1
    gamblingisfun
    I'm a 'handicapper'...
    gamblingisfun's Avatar Become A Pro!
    Join Date: 08-14-10
    Posts: 401
    Betpoints: 8632

    Need excel help for goal seek function

    I have a sports model that has many inputs for statistics, and various weightings for the importance of each one. I also have a point differential threshold that can be changed that makes it qualify as a bet. Each day of bets is on its own sheet that's connected to the weightings that are on one page. Each page does many calculations all based on those weightings. I can use goal seek to find the optimal weighting number to find what would make for the highest winning percentage for each individual weighting. Problem is goal seek seems to be for one at a time, and not all other ones connected to it. I'm wondering if there's a way to connect all the variables to find the optimal numbers for each one. Also if there's a limit to what excel can do since for each number changed it'll have to do many calculations on each page on like 100 different pages all at once. Thanks for the help in advance.

  2. #2
    Waterstpub87
    Slan go foill
    Waterstpub87's Avatar Become A Pro!
    Join Date: 09-09-09
    Posts: 4,043
    Betpoints: 7236

    Have you considered using the Solver add in instead of goal seek? You can set a max value for your objective, and select multiple cells as the input.

  3. #3
    benjy
    Untitled
    benjy's Avatar Become A Pro!
    Join Date: 02-19-09
    Posts: 2,158
    Betpoints: 2882

    I'm not familiar with goal seek but have used excel's Solver to find min/max of a function with multiple variables.

    Might be worth a look.

  4. #4
    thom321
    thom321's Avatar Become A Pro!
    Join Date: 06-17-11
    Posts: 112
    Betpoints: 4983

    I agree with using solver. Or better yet, if you find that Solver is a slow, try OpenSolver for Excel, http://opensolver.org/, a free open-source add-in that is faster and more powerful that Solver.

  5. #5
    gamblingisfun
    I'm a 'handicapper'...
    gamblingisfun's Avatar Become A Pro!
    Join Date: 08-14-10
    Posts: 401
    Betpoints: 8632

    I have tried the solver add in, maybe it's too many calculations for it to do because it doesn't come up with anything. It says the best solution is whatever I have everything set to when I start solver (even when I change numbers around). I'll have to look into the open solver thing. Hopefully it is more powerful

  6. #6
    gamblingisfun
    I'm a 'handicapper'...
    gamblingisfun's Avatar Become A Pro!
    Join Date: 08-14-10
    Posts: 401
    Betpoints: 8632

    Open solver is telling me that the solutions or whatever its trying to find are not linear. Also it seems to think that 0 is the best answer for everything lol

  7. #7
    Waterstpub87
    Slan go foill
    Waterstpub87's Avatar Become A Pro!
    Join Date: 09-09-09
    Posts: 4,043
    Betpoints: 7236

    Its probable not too many calculations. I have seen people use it to create optimal portfolios from like 50 different asset classes. Have many different variables do you have?

  8. #8
    gamblingisfun
    I'm a 'handicapper'...
    gamblingisfun's Avatar Become A Pro!
    Join Date: 08-14-10
    Posts: 401
    Betpoints: 8632

    It's a total of 20 different numbers that could be changed, and most would affect what the predicted score would be, and some would alter whether or not it qualifies as a bet or not. Could it be that all the info is on multiple different pages that are affected by one sheet of variables?

  9. #9
    HeeeHAWWWW
    HeeeHAWWWW's Avatar Become A Pro!
    Join Date: 06-13-08
    Posts: 5,487
    Betpoints: 578

    Time to learn how to use R or Python. Excel isn't suited for this sort of thing.

  10. #10
    Waterstpub87
    Slan go foill
    Waterstpub87's Avatar Become A Pro!
    Join Date: 09-09-09
    Posts: 4,043
    Betpoints: 7236

    It should not matter if the number can possibly change. The solver should compute based off the number that is in the specified cell.

    I am not sure what exactly you are trying to optimize

    Is the formula something like the looks like a regression with betas to specific statistics? so in this case you are trying to engineer the formula to give you the best looking bets?

    You could try R or Python. I much prefer excel with VBA even though it is like using a hammer on screw. The problem with R is that you can't see what you are working on, whereas you can see the cells and results in excel when you change formulas. R is not a terrible programming language to use though.

  11. #11
    benjy
    Untitled
    benjy's Avatar Become A Pro!
    Join Date: 02-19-09
    Posts: 2,158
    Betpoints: 2882

    Quote Originally Posted by Waterstpub87 View Post
    It should not matter if the number can possibly change. The solver should compute based off the number that is in the specified cell.

    I am not sure what exactly you are trying to optimize

    Is the formula something like the looks like a regression with betas to specific statistics? so in this case you are trying to engineer the formula to give you the best looking bets?

    You could try R or Python. I much prefer excel with VBA even though it is like using a hammer on screw. The problem with R is that you can't see what you are working on, whereas you can see the cells and results in excel when you change formulas. R is not a terrible programming language to use though.
    Love the hammer on a screw analogy.

    I second the suggestion(s) for either Python or R. Though I'd pick Python over R.

  12. #12
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,147
    Betpoints: 35447

    Quote Originally Posted by gamblingisfun View Post
    I have a sports model that has many inputs for statistics, and various weightings for the importance of each one. I also have a point differential threshold that can be changed that makes it qualify as a bet. Each day of bets is on its own sheet that's connected to the weightings that are on one page. Each page does many calculations all based on those weightings. I can use goal seek to find the optimal weighting number to find what would make for the highest winning percentage for each individual weighting. Problem is goal seek seems to be for one at a time, and not all other ones connected to it. I'm wondering if there's a way to connect all the variables to find the optimal numbers for each one. Also if there's a limit to what excel can do since for each number changed it'll have to do many calculations on each page on like 100 different pages all at once. Thanks for the help in advance.
    i have no idea what goal seek is but it's likely just a function someone wrote. If you know how to use VBA (sounds like you do not) - if you paste the function i can help.

Top