Need excel help for goal seek function

**Waterstpub87** · 06-07-16, 02:45 PM

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.

**benjy** · 06-07-16, 02:47 PM

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.

**thom321** · 06-07-16, 05:00 PM

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.

**gamblingisfun** · 06-07-16, 05:47 PM

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

**gamblingisfun** · 06-07-16, 07:16 PM

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

**Waterstpub87** · 06-07-16, 11:16 PM

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?

**gamblingisfun** · 06-08-16, 12:29 AM

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?

**HeeeHAWWWW** · 06-08-16, 06:44 AM

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

**Waterstpub87** · 06-08-16, 09:49 AM

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.

**benjy** · 06-08-16, 11:11 AM

Originally posted by Waterstpub87

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.

**a4u2fear** · 06-08-16, 11:58 AM

Originally posted by gamblingisfun

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.