# =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\left(x\right)=0$ $g\left(x\right)\ge 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,.. ).

Enter as a reference to a range. for Example, X1 for a single variable or X1:X3 for three variables. If your formulas or variables are not in a contiguous range, say X1, Z1, you can combine them and pass them as one reference as follows:

Use range union syntax (X1,Z1) . Supported in ExceLab 7 only
Use array constant syntax {X1,Z1}. Supported in Google Sheets only
ExceLab 365 supports only contiguous range input for multiple variables

### 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

 Key FACTOR Admissible Values (Real) > 0 Default Value 100 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.

 Key MAXFEV Admissible Values Positive integer Default Value 100000 Remarks Upper bound on the number of system evaluations.
 Key FTOL Default Value 1.0E-6 Remarks Error tolerance for the sum of residuals squares

 Key XTOL Default Value 1.0E-10 Remarks Error tolerance for the relative change in the solution vector

 Key JTOL Default Value 1.0E-14 Remarks Error tolerance for the orthogonality between the solution vector and the system Jacobian as measured by the cosine of the angle formed by the vectors

 Key ZTOL Default Value 1.0E-12 Remarks Effectively defines a numerical zero for comparisons.
 key JACSTEP Admissible Values (Real) 0 < JACSTEP < 0.1 Default Value The 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. Remarks The default value generally produces accurate approximations; however relaxing the default value may aid convergence on some problems with unknown Jacobian such as parameterized problems.

 key JACSCHEME Admissible Values (Integer) 1 for First order Euler forward scheme2 for Second order Euler forward scheme Default Value 1 Remarks First order scheme is generally sufficient.

If you have more than one variable, then run NLSOLVE as an array formula in an allocated range of cells. 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.

 A B 1 X1 Variable 1 result 2 X2 Variable 2 result 3 SSERROR Sum of square errors 4 ITRN Number of iterations 5 TIME (s) Calculation time in seconds
${10}^{4}{x}_{1}{x}_{2}-1=0$ ${e}^{-{x}_{1}}+{e}^{-{x}_{2}}-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.

 A X 1 =10^4*X1*X2-1 1 2 =EXP(-X1)+EXP(-X2)-1.0001 1

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

 A B 8 X1 9.10614674 9 X2 1.09816E-05 10 SSERROR 1.97215E-30 11 ITRN 16 12 TIME (s) 0.007
${x}_{1}+10{x}_{2}=0$ $\sqrt{5}\left({x}_{3}-{x}_{4}\right)=0$ ${\left({x}_{2}-2{x}_{3}\right)}^{2}=0$ $\sqrt{10}{\left({x}_{1}-{x}_{4}\right)}^{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

 F X 1 =X1+10*X2 1 2 =SQRT(5)*(X3-X4) 1 3 =(X2-2*X3)^2 1 4 =SQRT(10)*(X1-X4)^2 1

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

 C D 1 X1 6.02026E-12 2 X2 -6.02026E-13 3 X3 1.39508E-12 4 X4 1.39508E-12 5 SSERROR 4.70871E-45 6 ITRN 75 7 TIME (s) 0.022
$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.

 A X 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

 D E 20 X1 1 21 X2 10 22 X3 1 23 X4 5 24 X5 4 25 X6 3 26 SSERROR 5.86E-29 27 ITRN 76 28 TIME (s) 0.239

Consider the following system with one equation and two inequalities:

$10-{{x}_{1}}^{2}-{{2x}_{2}}^{2}-{{x}_{3}}^{2}+{x}_{1}=0$ $-5+{2{x}_{1}}^{2}+{{x}_{2}}^{2}+{{x}_{3}}^{2}+{2x}_{1}-{x}_{2}\ge 0$

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.

 A X 1 =10-X1^2-2*X2^2-X3^2+X1 1 2 =8-X1^2-X2^2-X3^2-X1+X2-X3 1 3 =-5+2*X1^2+X2^2+X3^2+2*X1-X2 1

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.

 D E 15 X1 1.096796361 16 X2 2.163300309 17 X3 0.730819849 18 SSERROR 1.19986E-27 19 ITRN 7 20 TIME (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 10628634, 10114812, 9892108 and 9286286.

© 2015-2021, ExcelWorks LLC
Boston, USA