{"id":945,"date":"2019-11-22T15:12:12","date_gmt":"2019-11-22T15:12:12","guid":{"rendered":"https:\/\/www.pyxll.com\/blog\/?p=945"},"modified":"2023-01-05T10:06:06","modified_gmt":"2023-01-05T10:06:06","slug":"a-better-goal-seek","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/a-better-goal-seek\/","title":{"rendered":"Multi-Dimensional Optimization: A Better Goal Seek"},"content":{"rendered":"\n

The code for the examples can be found in the optimization<\/i> folder of our examples repository.<\/a><\/p>\n\n\n\n

Improving on Excel’s Solver with Python<\/h2>\n\n\n\n

Excel comes with an installable add-in called the solver<\/a>, which is admirably well-suited to general optimization problems. You will find a simple description of the solver\u2019s capabilities in this series of articles<\/a>. This is a perfectly satisfactory way to solve many relatively simple models. In spreadsheet work the objective function is typically some model<\/em><\/a> describing real-world objects and relationships between them.<\/p>\n\n\n\n

Although versatile, like most packaged software the solver has its limitations<\/a> (limited choice of strategies, problem size and constraint size limitations being the most frequent issues), and so at some stage you may well find that you would like Excel to optimize models that are currently outside its capabilities.<\/p>\n\n\n\n

PyXLL users can deploy Python-based solutions to extend Excel\u2019s optimization abilities in any number of ways, tailored as necessary to their specific problems.<\/p>\n\n\n\n

In this article we consider how to make use of the many advanced optimization facilities readily available in the open source scipy.optimize<\/a> library. As a glance at that listing clearly shows there are optimization routines available to cope with all kinds of circumstances.<\/p>\n\n\n\n

[maxbutton name=”download”]<\/p>\n\n\n\n

Optimization principles<\/h3>\n\n\n\n

Optimization is very simple in principle. Then again, so is juggling, but the practice turns out to be rather difficult and far less straightforward than it seems in theory.<\/p>\n\n\n\n

Any process of optimization requires the finding of a minimum or maximum value for some function (the so-called objective function<\/em>) that produces a scalar output (to avoid ambiguity in maximisation). The difficulty of the problem is primarily determined by the dimensionality<\/em> of the problem: the number of scalar values in the function\u2019s input.<\/p>\n\n\n\n

Starting with chosen initial values the optimizer varies the inputs to the objective function to either maximise or minimise its value. A feedback loop directs the changes in the inputs according to the strategy the optimizer uses and the results of earlier evaluations.  This relatively simple statement, however, can hide a multitude of complexities.<\/p>\n\n\n\n

They arrive in the form of questions such as these:<\/p>\n\n\n\n