## Solving non-linear equations with two or more unknowns – 2

The previous post presented a simple but slow procedure for solving non-linear equations with two unknowns.  In this post I will describe a much faster method, that also has the advantage that it can simply be extended to work with any number of unknowns.

## Eval2.zip

For help with using array functions and user defined functions see: Using Array Functions and UDFs.

Looking again at the example from the previous post: The graph can be seen as a plan view of two 3D surfaces plotting the variation of Moment and Axial Force with Depth of Neutral Axis and Top Face Strain.  The blue line is the contour on the Moment surface where Moment = 30 kNm, and the red line is the contour on the Axial Force surface where Axial Force = 10 kN.  Where these two lines intersect is the point we are looking for.

If the curved surfaces at any point are approximated by the tangent plane at that point, then the contour for any desired value becomes a straight line, and it is easy to find the intersection of these two lines for the two chosen values.  In this example the solution requires solving a simultaneous equation with two variables, but the same approach is easily extended to any number of variables, using standard matrix algebra techniques.

The procedure for this solution method is:

• Write a function that will return the value of the two input functions to be solved for any given value of X and Y (where in the example X = top face strain and Y = depth of neutral axis)
• Using starting guessed values of X and Y calculate the value of the two functions at three locations: (X,Y),  (X+δX, Y), and (X, Y+δY)
• Using these three values find the slope of each surface in the direction of the X and Y axes.
• Using these slopes, set up two equations for the contour lines at the desired values.
• Solve the two simultaneous equations to find a revised estimate of X and Y.
• Repeat until the calculated X and Y values are sufficiently close to the target values.

VBA code for a User Defined Function (UDF) to carry out this procedure is shown below:

```Function MSolveT(Func As Variant, Target As Variant, Guess As Variant, Params As Variant, _
Optional Defaults As Variant, Optional CommaDec As Boolean = False)
'Solve Function for 2 unknowns

Dim Err As Double, LoopNum As Long
Dim Var1 As Double, Var2 As Double, Var1_2 As Double, Var2_2 As Double, Res1 As Variant
Dim Res2 As Variant, Res3 As Variant
Dim MaxLoops As Long, ResTol As Double, VarFact As Double
Dim SlopeA(1 To 2, 1 To 2) As Variant, Var1diff As Double, Var2diff As Double
Dim ResDiff(1 To 2, 1 To 1) As Double, ResA(1 To 2, 1 To 1) As Double, Fact As Double, ResOut(1 To 2, 1 To 2) As Variant

If IsMissing(Defaults) Then
MaxLoops = 20
ResTol = 0.0001
VarFact = 1.000001
Else
MaxLoops = Defaults(1, 1)
ResTol = Defaults(2, 1)
VarFact = Defaults(3, 1)
End If

If TypeName(Target) = "Range" Then Target = Target.Value2
If TypeName(Guess) = "Range" Then Guess = Guess.Value2

Var1 = Guess(1, 1)
Var2 = Guess(2, 1)

LoopNum = 0
Do
LoopNum = LoopNum + 1
' Evaluate both functions at the guessed estimates and small increments in the X and Y directions.

Var1_2 = Var1 * VarFact
Var2_2 = Var2 * VarFact

Guess(1, 1) = Var1
Guess(2, 1) = Var2
Res1 = MEval(Func, Params, Guess, , CommaDec)
Guess(1, 1) = Var1_2
Res2 = MEval(Func, Params, Guess, , CommaDec)
Guess(1, 1) = Var1
Guess(2, 1) = Var2_2
Res3 = MEval(Func, Params, Guess, , CommaDec)

Var1diff = Var1 * (VarFact - 1)
Var2diff = Var2 * (VarFact - 1)

' Create new slope array
SlopeA(1, 1) = (Res2(1, 1) - Res1(1, 1)) / Var1diff
SlopeA(1, 2) = (Res3(1, 1) - Res1(1, 1)) / Var2diff
SlopeA(2, 1) = (Res2(2, 1) - Res1(2, 1)) / Var1diff
SlopeA(2, 2) = (Res3(2, 1) - Res1(2, 1)) / Var2diff

' Solve for new estimate of X and Y values
Fact = SlopeA(1, 1) / SlopeA(2, 1)
SlopeA(2, 2) = SlopeA(2, 2) * Fact - SlopeA(1, 2)
ResDiff(1, 1) = Target(1, 1) - Res1(1, 1)
ResDiff(2, 1) = (Target(2, 1) - Res1(2, 1)) * Fact - ResDiff(1, 1)

ResA(2, 1) = ResDiff(2, 1) / SlopeA(2, 2)
ResA(1, 1) = (ResDiff(1, 1) - SlopeA(1, 2) * ResA(2, 1)) / SlopeA(1, 1)

Var1 = ResA(1, 1) + Var1
Var2 = ResA(2, 1) + Var2
Err = Abs(Target(1, 1) - Res1(1, 1))
Loop While LoopNum <= MaxLoops And Err > ResTol

' Recalculate function values with final X and Y values

If Err < ResTol Then
Guess(1, 1) = Var1
Guess(2, 1) = Var2
Res1 = MEval(Func, Params, Guess)

ResOut(1, 1) = Var1
ResOut(2, 1) = Var2
ResOut(1, 2) = Res1(1, 1)
ResOut(2, 2) = Res1(2, 1)
Else
ResOut(1, 1) = "Did not converge"
End If

MSolveT = ResOut
End Function
```

With the MSolveT UDF the two functions to be solved are entered as text on the spreadsheet.  These are then passed to MEval as an array of two text function, which are evaluated, and the results returned as a 2×1 array.  Note that the new MEval function can also be used as a UDF, and will work with any number of input functions.

Examples of the MSolveT UDF are shown in the screenshot below: Where the functions to be evaluated are too complex to enter as a single line of text on the spreadsheet, the MSolveF UDF may be used.  In this case the functions are evaluated by the VBA function named on the spreadsheet, which must return a variant array with (at least) 2 rows and 1 column.

In the example below the VBA function used, RCForceM, allows for the calculation of two layers of reinforcement, and also checks if the compression reinforcement is actually in the compression zone. Note that this function is provided for simple example purposes only. The Estress function in the Reinforced design Functions spreadsheet provides a closed form solution to the same problem, and also makes several checks that are not included in these simple examples; for instance that the neutral axis is within the depth of the section.

In the next post in this series the SolveF UDF will be used with a more complete function for evaluating the force and moment on a reinforced concrete section, including allowance for non-linear stress-strain curves for the steel and concrete, and use of a tensile stress curve for the concrete, to model tension stiffening.

This entry was posted in Beam Bending, Concrete, Excel, Maths, Newton, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

### 5 Responses to Solving non-linear equations with two or more unknowns – 2

1. Achille says:

All examples you gave end up in error like ” #VALUE! “. It may be a problem concerning decimal separator (note: I use european standard)?

Like

• dougaj4 says:

I don’t think the separator is the problem. I set the separators to European under Options – advanced, and it still works for me.

If you open the VB Editor (press Alt-F11), then look under Tools-References, make sure that Microsoft Scripting Runtime is selected.
If it isn’t, then selecting may solve the problem.

If that doesn’t fix it please e-mail a copy of your file to dougaj4 at the usual gmail and I will have a look.

By the way, there was an issue with the CommaDec option, but that only arises when the function text contains a number with a decimal separator, so I don’t think it is the problem in this case. I will upload a version with this problem fixed (2.03) later today.

Like

2. Georg says:

Hi Doug, is there a special reason that prevents you from harnessing the Levenberg-Marquardt method (https://en.wikipedia.org/wiki/Levenberg–Marquardt_algorithm)?? Your non-linear functions look pretty smooth so that they could be well approximated by analytical functions the derivatives of which are known analytically as well. LMA provides you with estimates of the co-variance matrix between all the parameters from which you can derive information on confidence intervals of the fitted parameter values. In case the parameters are nearly independent of each other, it is no problem to fit functions of up to about 5 to 8 parameters using LMA.

Like

• dougaj4 says:

Hi Georg, I’m not familiar with the Levenberg-Marquardt method (at least not by name), but having had a quick look on Wikipedia I’m not sure that it would be appropriate for what I want. The functions I used were just for the sake of a simple example; in fact I can reduce the second example problem to solving a single cubic equation, but that approach doesn’t work when I use non-linear materials properties, and I was looking for a method that would work for anything. The approach I have used seems to me the most common, and was really the only one I found that was documented in a way I could understand, and seems to work well for the problems I have tried it on (which the Excel Solver didn’t). But if you have any suggestions for alternatives, I would be interested to have a look (time permitting :))

Like

• dougaj4 says:

Georg – see comment from Lori Miller:
https://newtonexcelbach.wordpress.com/2015/10/20/solving-non-linear-equations-with-two-or-more-unknowns-5/#comment-9320

You might also like to have a look at the Python/SciPy version of the solver function, which has an option for the Levenberg-Marquardt method (amongst many others).

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.