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.