# 9. Nonlinear optimization of univariate function

Note – The example worksheets are available for download. See 1. How to obtain and use example worksheets.

Example of solving a nonlinear optimization of univariate function, i.e. finding a minimum point of general one-dimensional nonlinear function by XLPack is shown below.

### Example

Let’s obtain the minimum point of the following equation near x = 1.

f(x) = x^{3}- 2x - 5 = 0

As shown in the figure below, there is no global minimum of f(x) (diverging for -∞). However, there is a local minimum near x=1.

### Solving problem with VBA program (1)

The following is an example code using VBA library function **Dfmin**.

```
Function F(X As Double) As Double
F = X ^ 3 - 2 * X - 5
End Function
Sub Start()
Dim Ax As Double, Bx As Double, Tol As Double, X As Double
'--- Input data
Ax = 0
Bx = 1.5
Tol = 0.000000000000001 '1e-15
'--- Compute min. point of equation
Call Dfmin(Ax, Bx, AddressOf F, Tol, X)
'--- Output min. point
MsgBox X
End Sub
```

The function **Dfmin** finds the minimum point in the interval between given Ax and Bx. Only one minimum is assumed in the interval. In this example, the local minimum exists near x = 1. Then, Ax = 0 and Bx = 1.5 are set in the program.

The result X = 0.81650 is obtained by running this program.

### Solving problem with VBA program (2)

The following is an example code using the reverse communication version VBA library subroutine **Dfmin_r**.

```
Sub Start()
Dim Ax As Double, Bx As Double, Tol As Double, X As Double
Dim XX As Double, YY As Double, IRev As Long
'--- Input data
Ax = 0
Bx = 1.5
Tol = 0.000000000000001 '1e-15
'--- Compute min. point of equation
IRev = 0
Do
Call Dfmin_r(Ax, Bx, Tol, XX, YY, IRev)
If IRev <> 0 Then YY = XX ^ 3 - 2 * XX - 5
Loop While IRev <> 0
X = XX
'--- Output min. point
MsgBox X
End Sub
```

Instead of defining the object function as the external VBA function, when returned with IRev = 1, you need to compute the object function value at XX, enter it in YY and call **Dfmin_r** again. For further details of reverse communication version, see here.

The same result with above is obtained by this program.

### Solving problem with Solver

“Univariate Nonlinear Optimization” of XLPack solver add-in can be used to solve this problem. The formula (=B6^3-2*B6-5) is entered in cell B7.

Please refer to here for how to use solver.