I have a model wherein I’d like to do analysis on one of the inputs by holding an output constant. I do this via a VBA program wherein I change several of the inputs, then use VBA to do an Excel Goal Seek on my input variable with my output variable as a constant. The problem is that the goal seek function sucks - it frequently fails to find my results, or worse, even though I’m looking for a double digit number, starts guessing 3 and 4 digit numbers, and then gets into the millions and beyond. I’ve got a check in there such that if this happens, goal seek will eventually reset itself and I’ll insert a new semi-random number for it to try again on. The problem is I’m running hundreds of scenarios, and when this goal seek foul up happens on about half the scenarios, it exponentially increases the amount of time the program needs to run. About half the time it finds the solution within 5 seconds. Any suggestions on how to improve this or a better way to goal seek?
Have you tried substituting Solver for Goal Seek?
I believe you can give goal seek an initial guess. It also improves performance if you set Application.ScreenUpdating = False.
Well goal seek starts guessing based on the value in the cell, and I’ve already improved performance dramatically by making it guess “smartly” by looking at prior results and picking a likely relevant one to guess on. The solver idea is good, but do you think it would make that much of a difference? Are they using largely the same algo’s?
Solver is 100x better than goalseek. They only leave goalseek in there for backward compatibility reasons. A bit more of a learning curve, but well worth it for situations like this.
Solver doesn’t work for me. Won’t find a feasible solution. I’ve tried messing with the sensitivity settings and it still won’t work. Is it because my value is too precise? I need to solve for a value with a MoE of about 10 bps.
You should rapidly move to a better forum for this discussion (i.e., Excel or something, not AF). As parting questions: Are you sure there’s a solution? And is your objective function well behaved? (e.g. differentiable, no local minima, etc.)
Will do, thanks. There is a solution, because I can easily figure it our from guess and check. Here’s the essentially problem: I’m solving for a target return, but I need that target return to be achieved in a very specific time frame, and based upon my inputs that target return could be achieved sooner or later, so it’s kind of a moving target. I’m quite sure my function is not well-behaved.