Goal Seek v Solver?

[ColW]

Hi All,

I built a goal seek macro that pushes 1 number through 150,000 formulas. I've allowed some tolerance to enable the solution to always be found.

The problem I'm having is that the goal seek finds the solution sometimes and not others, quite temperamental (like my ex gf haha).

On occasion where goal seek does not find the answer I can go in and find the answer through manual iteration.

Part of the problem is that goal seek searches too wide when the answer will always be in a relatively narrow window. Should I be using Solver for this and narrowing the search bandwidth/using an alternative method?

Cheers,

Col

[davsth]

It seems that it would be worth a try, especially if the solution can be found by interpolation

[ColW]

The problem that I had when I tried the solver path was that it never solves - is that because its harder to drive or is there an easier way to get a result that I'm not thinking of?

[MrShorty]

Difficult to speak to specifics with a generic question. In general, I prefer Solve over Goal Seek, because I find that Solver's algorithms are more robust. Goal seek uses a basic Newton-Raphson type algorithm, but Solver has a more robust implementation of a similar algorithm as well as other algorithms that may be more suitable. It is also possible to set conditions and constraints to prevent Solver from looking outside of the allowed/expected solution range.

Some of the solution might depend on why the root finding algorithm is temperamental. Sometimes, when I know that the solution will always be within a certain range, I will program my own algorithm and use something like a bisection or false position algorithm (https://en.wikipedia.org/wiki/Root-f...keting_methods ). Are you interested in programming your own root finding algorithm, or do you want to stick with using Goal Seek or Solver?

[ColW]

Incredible response thanks, my preference is to stick with Solver purely because programming my own root finding algorithm could send me down an uncommercial rabbit hole.

I've 'played' with Solver for this problem and its not found me a solution many times. I'll work with Solver some more to see if I an create the right environment that helps triangulate a result. Will post a further message here if I get stuck.

[Alf]

Perhaps you could upload a small sample of your file for forum members to test and see if somebody could find a more user friendly setup for solver.

Alf