+ Reply to Thread
Results 1 to 6 of 6

Goal Seek v Solver?

  1. #1
    Registered User
    Join Date
    06-11-2019
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    3

    Cool Goal Seek v Solver?

    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
    Last edited by ColW; 06-11-2019 at 04:45 AM.

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,026

    Re: Goal Seek v Solver?

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

  3. #3
    Registered User
    Join Date
    06-11-2019
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    3

    Re: Goal Seek v Solver?

    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?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,586

    Re: Goal Seek v Solver?

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    06-11-2019
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    3

    Re: Goal Seek v Solver?

    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.

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,702

    Re: Goal Seek v Solver?

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Solver, goal seek or more simple formula
    By faodavid in forum Excel General
    Replies: 5
    Last Post: 09-16-2015, 10:44 AM
  2. Solver or Goal Seek Question
    By ljochmann in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2014, 04:57 PM
  3. Goal Seek, Solver, and an IRR Workaround
    By Jakila2 in forum Excel General
    Replies: 7
    Last Post: 08-21-2013, 03:48 PM
  4. [SOLVED] Using Solver vs Goal Seek
    By JimDandy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-26-2013, 08:18 AM
  5. Goal Seek and SOLVER
    By dalebirrell in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 04-22-2010, 05:59 AM
  6. Excel Goal Seek vs. Solver
    By Adrian T in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-31-2005, 10:40 AM
  7. [SOLVED] goal seek vs solver
    By neoschenker in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-03-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1