XLPack Solver – Multivariate Nonlinear Optimization

Minimize the nonlinear function with multiple variables.

Let’s find the minimum point of the following example equation.

f(x1,x2) = 100*(x2 – x1^2)^2 + (1 – x1)^2 (Rosenbrock function)

The solver program will write the value of x1 and x2 into the variable value cells (B8 and B9 in this case). The function value cell (C8 in this case) must contain the formula to compute f(x1,x2) from x1 and x2. To solve this example, the formula for C8 is =100*(B9-B8^2)^2+(1-B8)^2.

Derivative cells contain the formulas to compute df/dxi. These are necessary only when Mng is selected as the solver program.

Initial value cells contain the initial values of x1 and x2. If these are not specified (range remains blank), initial values are assumed to be contained in the variable value cells.

The obtained solutions are output to the output cell range. If it is not specified (range remains blank), the solutions will be output to the variable value cells.

Optif0, Mnf, Mng or Subplex can be selected as the solver program.

The standard value of tolerance is 1.0e-8. The tolerance value will be set to Xctol of Mnf and Mng, and Tol of Subplex. This value will not be used by Optif0 since the tolerance is automatically set within the routine.

Click “Compute”. Then the solutions will be computed and output.

The cell ranges can be specified as larger than required. In that case, only the necessary range will be used from left upper corner.

Nonlinear functions may have several local minimum points. The different minimum point may be obtained depending on the initial values. The different point may be obtained if the solver program is different even if the initial values are same. It is therefore recommended to give the initial values nearest to the desired minimum point.

If the minimum point is not obtained, #NUM! error will be output. The major suspected causes in that case are as follows: The algorithm was not converged due to the bad initial values; The given equation has no minimum point.

Please refer to here for “Save/Restore” button.

When “Help” button is clicked, this page will be displayed if the network connection is available.

The “?” button of right upper corner will not work correctly. Please use “Help” button.