# Goal Seek Calculation

1. ## 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?

2. ## 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.

3. ## 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.

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

#### 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