+ Reply to Thread
Results 1 to 3 of 3

Goal Seek Calculation

  1. #1
    Registered User
    Join Date
    11-25-2013
    Location
    San Jose, California, US
    MS-Off Ver
    Excel 2010
    Posts
    9

    Goal Seek Calculation

    Hi,

    I am trying to figure out the calculation steps that Goal Seek is doing behind the scenes so I can create the formula rather than clicking on goal seek whenever I need to make a change. I need to maintain certain weeks of supply every week depending on the sales. Let's say i want to maintain 5 weeks of supply every week, I would like to insert a column for "Suggested To Order' column that would change if weeks of supply goes below 5. In this scenario I would do a Goal Seek, but I want to automate the calculation so I don't have to do this step every week. Any thoughts?

    Thanks for your help!

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

    Re: Goal Seek Calculation

    You give no detail about how you calculate 5 weeks supply. Before we get into the details of how Goal Seek works its magic, I would suggest that, if the problem can be solved algebraically, that will probably be a lot easier than trying to program the numerical algorithms used by Goal Seek.

    It seems that many around here find it easier to use VBA to automate Goal seek in a macro rather than try to write the goal seek algorithms. I don't know what VBA coding skills you have, but it might be worth considering.

    Goal seek uses a numerical algorithm called Newton's method or the Newton Raphson method. It is a numerical algorithm for solving equation that is especially useful for equations that cannot be solved algebraically. This tutorial seems to be a pretty good tutorial, complete with a nice Java applet that illustrates how the algorithm works (http://www.cs.utah.edu/~zachary/isp/...ot/Newton.html). When you are familiar with the algorithm, it is not overly difficult to program in - either using spreadsheet formulas or in a language like VBA. In a spreadsheet, you usually want to create a "circular reference" as part of the algorithm, which does add a level of complexity to the spreadsheet that most don't get into.

    From that information, consider which approach you want to try to use, and we should be able to help you implement something.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    11-25-2013
    Location
    San Jose, California, US
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Goal Seek Calculation

    Thank you! I have very basic VBA skills.. I will look into the link you sent me regarding the algorithm and see if I can make it work.

+ 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. Faster calculation for multiple automatic goal seek
    By lockd in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-26-2013, 09:30 AM
  2. Replies: 3
    Last Post: 07-27-2012, 01:44 PM
  3. Goal Seek VBA with relative 'Goal' parameter
    By alirulez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2012, 07:19 PM
  4. Goal Seek Formula But Not Using Goal Seek
    By cady923 in forum Excel General
    Replies: 1
    Last Post: 08-05-2011, 03:53 PM
  5. vba goal seek 2
    By csw78 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-12-2005, 03:24 PM

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