Solving Nonlinear Equations and Inequalities in Excel

Syntax

=NLSOLVE(eqns, vars, [options])


Use NLSOLVE to find a least-error solution to a system of algebraic equations and optionally inequalities constraints in the form: f ( x ) = 0 g ( x ) 0

Starting form an initial guess for the variables x=(x1, x2,.. ), NLSOLVE attempts to find the optimal solution that satisfies the inequality constraints g=(g1, g2,.. ) while minimizing the sum of the square errors of the equality constraints f=(f1, f2,.. ).

With options, you can specify the number of inequality constraints if present, override the algorithm parameters, as well as supply an analytic Jacobian for the system of equations.

NLSOLVE supports dynamical systems (see Dynamical Optimization).

Required Inputs

eqns a reference to the system formulas (f1, f2,..,g1, g2,..).

If your system includes inequality constraints, they must be ordered last.

vars a reference to the system variables (x1, x2,.. ).

Optional Inputs

nge number of inequality constraints in the system. Default is 0.

ctrl a set of key/value pairs for algorithmic control as detailed below.

Description of key/value pairs for algorithmic control

KeyFACTOR
Admissible Values (Real)> 0
Default Value100
Remarks
  • Controls progressive adjustments for the initial trial step.
  • This parameter can be very influential for difficult problems. If you encounter convergence difficulties try smaller or even larger values for Factor.

KeyMAXFEV
Admissible ValuesPositive integer
Default Value100000
RemarksUpper bound on the number of system evaluations.
KeyFTOL
Default Value1.0E-6
RemarksError tolerance for the sum of residuals squares

KeyXTOL
Default Value1.0E-10
RemarksError tolerance for the relative change in the solution vector

KeyJTOL
Default Value1.0E-14
RemarksError tolerance for the orthogonality between the solution vector and the system Jacobian as measured by the cosine of the angle formed by the vectors

KeyZTOL
Default Value1.0E-12
RemarksEffectively defines a numerical zero for comparisons.
keyJACSTEP
Admissible Values (Real)0 < JACSTEP < 0.1
Default ValueThe default step is computed dynamically based on machine accuracy, preset limits and function metric. For an order(1) function it is approximately 1.0e-8.
RemarksThe default value generally produces accurate approximations; however relaxing the default value may aid convergence on some problems with unknown Jacobian such as parameterized problems.

keyJACSCHEME
Admissible Values (Integer)

1 for First order Euler forward scheme

2 for Second order Euler forward scheme

Default Value1
RemarksFirst order scheme is generally sufficient.
Unformatted Output
If you have more than one variable, then NLSOLVE must be run as an array formula in an allocated range of cells. The allocated output array size must have at least as many cells as the number of variables, n. The results are reported in the same variables order specified in parameter number 2.
A
1 Variable 1 result
2 Variable 2 result
AB
1 Variable 1 result Variable 2 result
Formatted Output
If you allocate a 2-column array with n+3 rows for the output, NLSOLVE automatically formats the solution for you and reports the additional information as shown below.
AB
1X1 Variable 1 result
2X2 Variable 2 result
3SSERRORSum of square errors
4ITRN Number of iterations
5TIME (s)Calculation time in seconds
104x1x2-1=0 e-x1+e-x2-1.0001=0

The system has a solution at (9.106,1.098e-5). We define the system LHS equations in A1:A2 using X1 and X2 for variables with 1 for the initial guess as shown in Table 1.

Table 1
AX
1=10^4*X1*X2-11
2=EXP(-X1)+EXP(-X2)-1.00011

Next, we evaluate the array formula =NLSOLVE(A1:A2, X1:X2) in range A8:B12 and obtain the result shown in Table 2

Table 2
AB
8X19.10614674
9X21.09816E-05
10SSERROR1.97215E-30
11ITRN16
12TIME (s)0.007
x1+10x2=0 5x3-x4=0 (x2-2x3)2=0 10(x1-x4)2=0

The system has the only solution at (0, 0, 0, 0) which is not an attraction point. We define the system LHS equations in F1:F4 using X1:X4 for variables with 1 specified for initial guess as shown in Table 1

Table 1
FX
1=X1+10*X21
2=SQRT(5)*(X3-X4)1
3=(X2-2*X3)^21
4=SQRT(10)*(X1-X4)^21

Next, we evaluate the array formula =NLSOLVE(F1:F4, X1:X4) in range C1:D7 and obtain the result shown in Table 2.

Table 2
CD
1X16.02026E-12
2X2-6.02026E-13
3X31.39508E-12
4X41.39508E-12
5SSERROR4.70871E-45
6ITRN75
7TIME (s)0.022

Supplying optional analytic Jacobian

To demonstrate the effect of supplying analytic system Jacobian on the solution accuracy, we define the system Jacobian in array D1:G4 as shown in Table 3

Table 3
DEFG
111001
200=SQRT(5)=-SQRT(5)
30=2*(X2-2*X3)=-4*(X2-2*X3)0
4=2*SQRT(10)*(X1-X4)00=-2*SQRT(10)*(X1-X4)

We evaluate the updated array formula =NLSOLVE(F1:F4, X1:X4, , , D1:G4) skipping over parameters 3 and 4 and passing in the Jacobian matrix in optional argument 5. The new solution shown in Table 4 shows evident improvement in reduced errors for this problem.

Table 4
CD
1X11.77636E-15
2X2-1.77636E-16
3X33.55271E-16
4X43.55271E-16
5SSERROR4.14055E-59
6ITRN50
7TIME (s)0.038
x3e-0.1x1-x4e-0.1x2+x6e-0.1x5-e-0.1+5e-1-3e-0.4=0 x3e-0.2x1-x4e-0.2x2+x6e-0.2x5-e-0.2+5e-2-3e-0.8=0 x3e-0.3x1-x4e-0.3x2+x6e-0.3x5-e-0.3+5e-3-3e-1.2=0 x3e-0.4x1-x4e-0.4x2+x6e-0.4x5-e-0.4+5e-4-3e-1.6=0 x3e-0.5x1-x4e-0.5x2+x6e-0.5x5-e-0.5+5e-5-3e-2=0 x3e-0.6x1-x4e-0.6x2+x6e-0.6x5-e-0.6+5e-6-3e-2.4=0

The system has a solution at (1, 10, 1, 5, 4, 3). We define the system equations in A1:A6 using X1:X6 as variables with a value of 1 for initial guess as shown in Table 1.

Table 1
AX
1=X3*EXP(-0.1*X1)-X4*EXP(-0.1*X2)+X6*EXP(-0.1*X5)-EXP(-0.1)+5*EXP(-1)-3*EXP(-0.4)1
2=X3*EXP(-0.2*X1)-X4*EXP(-0.2*X2)+X6*EXP(-0.2*X5)-EXP(-0.2)+5*EXP(-2)-3*EXP(-0.8)1
3=X3*EXP(-0.3*X1)-X4*EXP(-0.3*X2)+X6*EXP(-0.3*X5)-EXP(-0.3)+5*EXP(-3)-3*EXP(-1.2)1
4=X3*EXP(-0.4*X1)-X4*EXP(-0.4*X2)+X6*EXP(-0.4*X5)-EXP(-0.4)+5*EXP(-4)-3*EXP(-1.6)1
5=X3*EXP(-0.5*X1)-X4*EXP(-0.5*X2)+X6*EXP(-0.5*X5)-EXP(-0.5)+5*EXP(-5)-3*EXP(-2)1
6=X3*EXP(-0.6*X1)-X4*EXP(-0.6*X2)+X6*EXP(-0.6*X5)-EXP(-0.6)+5*EXP(-6)-3*EXP(-2.4)1

Next, we evaluate the array formula =NLSOLVE(A1:A6, X1:X6) in range D20:E28 and obtain the result shown in Table 2

Table 2
DE
20X11
21X210
22X31
23X45
24X54
25X63
26SSERROR5.86E-29
27ITRN76
28TIME (s)0.239

Consider the following system with one equation and two inequalities:

10-x12-2x22-x32+x1=0 8-x12-x22-x32-x1+x2-x3 0 -5+2x12+x22+x32+2x1-x20

We define the system LHS equations in A1:A3 using X1:X3 for variables with 1 for the initial guess as shown in Table 1. Note that the inequalities formulas are listed after the equality formula as required by the solver.

Remember that equations and inequalities formulas are defined with respect to zero on one side, and any inequalities are interpreted as greater than zero by the solver.

Table 1
AX
1=10-X1^2-2*X2^2-X3^2+X11
2=8-X1^2-X2^2-X3^2-X1+X2-X31
3=-5+2*X1^2+X2^2+X3^2+2*X1-X21

Next, we evaluate the array formula =NLSOLVE(A1:A3, X1:X3, 2) in range D15:E20. Note that we pass 2 in the 3rd parameter to specify that the last 2 formulas passed in parameter 1 are inequalities.

Table 2
DE
15X11.096796361
16X22.163300309
17X30.730819849
18SSERROR1.19986E-27
19ITRN7
20TIME (s)0.009

NLSOLVE is based on the Levenberg-Marquardt method.

  • Kenneth Levenberg. A Method for the Solution of Certain Non-Linear Problems in Least Squares. Quarterly of Applied Mathematics. (2):164-201, 1944.
  • Donald Marquardt. An Algorithm for Least-Squares Estimation of Nonlinear Parameters. SIAM Journal on Applied Mathematics 11 (2):431-441, 1963.
Question or Comment? Email us:
support @ excel-works.com
ExceLab: Transforming Excel into a Calculus Power House
ExceLab functions and methods are protected by USA Patents 9286286, 9892108, 10114812 and pending.
© 2015-2019, ExcelWorks LLC
Boston, USA