Solving Nonlinear Equations and Inequalities in Excel

Syntax

`=NLSOLVE(eqns, vars, [options])`

Description

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) \geq 0$

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

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).

Inputs

Required Inputs

eqns a reference to the system formulas (f₁, f₂,..,g₁, g₂,..).

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

vars a reference to the system variables (x₁, x₂,.. ).

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

Algorithm Parameters

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.

Error Controls

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.

Jacobian Approximation

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 scheme 2 for Second order Euler forward scheme
Default Value	1
Remarks	First order scheme is generally sufficient.

Output Format

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

Examples

Collapse all examples

Example 1: Powell’s badly scaled problem

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.

Table 1
	A	X
1	=10^4X1X2-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

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

Example 2: Powell's singular function

x_{1} + 10 x_{2} = 0

\sqrt{5} (x_{3} - x_{4}) = 0

{(x_{2} - 2 x_{3})}^{2} = 0

\sqrt{10} {(x_{1} - x_{4})}^{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
	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.

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

Example 3: Biggs problem

{x_{3} e}^{- 0.1 x_{1}} - x_{4} e^{- 0.1 x_{2}} + x_{6} e^{- 0.1 x_{5}} - e^{- 0.1} + 5 e^{- 1} - 3 e^{- 0.4} = 0

{x_{3} e}^{- 0.2 x_{1}} - x_{4} e^{- 0.2 x_{2}} + x_{6} e^{- 0.2 x_{5}} - e^{- 0.2} + 5 e^{- 2} - 3 e^{- 0.8} = 0

{x_{3} e}^{- 0.3 x_{1}} - x_{4} e^{- 0.3 x_{2}} + x_{6} e^{- 0.3 x_{5}} - e^{- 0.3} + 5 e^{- 3} - 3 e^{- 1.2} = 0

{x_{3} e}^{- 0.4 x_{1}} - x_{4} e^{- 0.4 x_{2}} + x_{6} e^{- 0.4 x_{5}} - e^{- 0.4} + 5 e^{- 4} - 3 e^{- 1.6} = 0

x_{3} e^{- 0.5 x_{1}} - x_{4} e^{- 0.5 x_{2}} + x_{6} e^{- 0.5 x_{5}} - e^{- 0.5} + 5 e^{- 5} - 3 e^{- 2} = 0

{x_{3} e}^{- 0.6 x_{1}} - x_{4} e^{- 0.6 x_{2}} + x_{6} e^{- 0.6 x_{5}} - e^{- 0.6} + 5 e^{- 6} - 3 e^{- 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
	A	X
1	=X3EXP(-0.1X1)-X4EXP(-0.1X2)+X6EXP(-0.1X5)-EXP(-0.1)+5EXP(-1)-3EXP(-0.4)	1
2	=X3EXP(-0.2X1)-X4EXP(-0.2X2)+X6EXP(-0.2X5)-EXP(-0.2)+5EXP(-2)-3EXP(-0.8)	1
3	=X3EXP(-0.3X1)-X4EXP(-0.3X2)+X6EXP(-0.3X5)-EXP(-0.3)+5EXP(-3)-3EXP(-1.2)	1
4	=X3EXP(-0.4X1)-X4EXP(-0.4X2)+X6EXP(-0.4X5)-EXP(-0.4)+5EXP(-4)-3EXP(-1.6)	1
5	=X3EXP(-0.5X1)-X4EXP(-0.5X2)+X6EXP(-0.5X5)-EXP(-0.5)+5EXP(-5)-3EXP(-2)	1
6	=X3EXP(-0.6X1)-X4EXP(-0.6X2)+X6EXP(-0.6X5)-EXP(-0.6)+5EXP(-6)-3EXP(-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
	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

Example 4: Solving a system with inequality constraints

Consider the following system with one equation and two inequalities:

10 - {x_{1}}^{2} - {2 x}_{2}^{2} - {x_{3}}^{2} + x_{1} = 0

8 - {x_{1}}^{2} - {x_{2}}^{2} - {x_{3}}^{2} - x_{1} + x_{2} - x_{3} \geq 0

- 5 + {2 x_{1}}^{2} + {x_{2}}^{2} + {x_{3}}^{2} + {2 x}_{1} - x_{2} \geq 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.

Table 1
	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+2X1^2+X2^2+X3^2+2X1-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.

Table 2
	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

Algorithms

NLSOLVE is based on the Levenberg-Marquardt method.

References

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.

	A	X
1	=X3EXP(-0.1X1)-X4EXP(-0.1X2)+X6EXP(-0.1X5)-EXP(-0.1)+5EXP(-1)-3EXP(-0.4)	1
2	=X3EXP(-0.2X1)-X4EXP(-0.2X2)+X6EXP(-0.2X5)-EXP(-0.2)+5EXP(-2)-3EXP(-0.8)	1
3	=X3EXP(-0.3X1)-X4EXP(-0.3X2)+X6EXP(-0.3X5)-EXP(-0.3)+5EXP(-3)-3EXP(-1.2)	1
4	=X3EXP(-0.4X1)-X4EXP(-0.4X2)+X6EXP(-0.4X5)-EXP(-0.4)+5EXP(-4)-3EXP(-1.6)	1
5	=X3EXP(-0.5X1)-X4EXP(-0.5X2)+X6EXP(-0.5X5)-EXP(-0.5)+5EXP(-5)-3EXP(-2)	1
6	=X3EXP(-0.6X1)-X4EXP(-0.6X2)+X6EXP(-0.6X5)-EXP(-0.6)+5EXP(-6)-3EXP(-2.4)	1

ExcelWorks LLC

Solving Nonlinear Equations and Inequalities in Excel

Syntax

=NLSOLVE(eqns, vars, [options])

Required Inputs

Optional Inputs

`=NLSOLVE(eqns, vars, [options])`