• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Goal Seek iterations

Baldwreck

New Member
I have been stumped for years on this one. Whist it should be possible to limit the number of iterations that Goal Seek makes by default (100) through either FIle>Options>Formulas>Maximum Iterations - or a short VBA instruction Application.MaxIterations = X: for some reason if you put in the iterations to be less than 100 excel Goal Seek still defaults to 100. It will happily accept any number above 100 but not below it. The reason this is important to me is that if I could halve the number of iterations I could do this particular work in half the time. The alternative is to upgarde to a super fast CPU but that seems like the proverbial sledgehammer to crack a nut. If any one out there has any ideas they will be very gratefully received or alternativley if someone has influence at Microsoft to change the default that would be appreciated too.
 
@Baldwreck,

You might consider using the Solver add-in. It comes with Excel, but you may have to enable it if you have not used it before. It has more available options and controls making it somewhat more versatile than Goal Seek.

Hope that helps.

Regards,
Ken
 
Ken
Thanks for the fast response - sadly Solver does not seem to work any the better or faster - if only!
Baldwreck
 
It sounds like you may need to ensure that the problem you are trying to solve is well-defined. There will be some cases in which any iterative method will fail to converge (therefore always going to the iteration limit). If the problem can be properly defined and/or constrained the calculation engine (i.e., Goal Seek or Solver) should be pretty efficient. On the other hand, too much constraint (such as forcing very small increments) can also hamper efficiency.

Of course, I can only give general suggestions since I don't know the details of the problem you are working on.

Regards,
Ken
 
Ken - thank you.
It may well be that I'm trying to get goal seek to do something it was never designed to do. The issue remains that if goal seek could be set to do less that 100 iterations I would be a happy bunny - my experience with using it in other problems is that provided there is an answer it would be pretty unusul for goal seek to take more than about 25 iterations to find a solution.

Regards

Baldwreck
 
Back
Top