## Recipe 9.5. Tackling Nonlinear Systems of Equations## ProblemYou have a system of nonlinear equations that you need to solve. ## SolutionFrame the problem in terms of residuals and use Solver to minimize the residual, thus arriving at a solution. ## DiscussionYou can use the same Solver-based techniques discussed in Recipe 9.4 to solve systems of nonlinear equations. By way of example, consider the two equations: Let's assume you want to find a value, or values, of x satisfying both equations simultaneously. As you can see from the plot of these two equations shown in Figure 9-18, there are actually two solutions. ## Figure 9-18. Nonlinear equations plotYou have some choices as to how you approach a solution to this problem. If you can readily solve these equations for y (which you can in this case), then you can equate them and rewrite them in the form 0 = f(x) - g(x) and then use the techniques discussed in Recipe 9.2 to find a solution. If you have more than two equations or you can't easily solve them in terms of a common variable, then you can use the minimization of residuals method discussed in Recipe 9.4, specifically in the section "Using Solver to minimize residuals," to find a solution. Using this latter approach, I found two xs that satisfy this system, namely, |

## Recipe 9.6. Using Classical Methods for Solving Equations## ProblemYou've seen how to leverage Excel's built-in features to solve linear and nonlinear equations and systems, but you'd still like to know how to go about implementing some classical equation-solving algorithms in Excel. ## SolutionYou can use VBA to program any algorithm, just as you might in other programming languages. In the discussion to follow, I'll show you how to implement two classical methods in VBA: Newton's method and the secant method. ## DiscussionThe examples in this recipe will show how you might implement Newton's method and the secant method. These are well-known methods and are treated in most advanced mathematics and numerical methods texts. Further, you can find implementations of these methods in various languages, such as Fortran and C, on the Web. It's not a great leap to implement these methods in VBA in the form of a custom function, which you can then call from an Excel spreadsheet. For the examples in this recipe, let's reconsider the cubic polynomial equation already discussed in Recipe 9.1. The equation is repeated here for convenience: A quick plot of this equation, as shown earlier in Figure 9-6, shows about where these three roots lie. You could easily use Solver to accurately find each of these roots. Or, you could use Newton's method or the secant method (or any of a number of other methods you could program), as discussed in the remainder of this recipe. ## Newton's methodNewton's method is based on the idea of estimating values of x that are roots of the equation by taking the intersection of a line tangent to the curve under consideration at some assumed starting x-value. Subsequent estimates are made repeatedly using this approach until the estimate x-value converges on the true root. The basic formula for predicting each new x-value as you approach a root is: Notice that in Newton's method you must be able to compute, or estimate, the first derivative of the curve under consideration. In the example considered here, we have an equation that allows you to explicitly compute the derivative. In other cases where you can't easily compute the derivative, or where it is impossible to do so explicitly (such as when part of your function relies on tabular data), you have to resort to numerical derivatives. Recall from the introduction to this chapter that Solver uses numerical derivatives and allows you to select either forward differencing or central differencing (refer to Recipe 10.6 for a discussion of these and other techniques). The structure of the above equation should give you some hints as to how you might implement Newton's method. First, we need a subroutine to evaluate the function under consideration. Second, we need another subroutine to evaluate the derivative of that function. Third, we need a subroutine that will iteratively apply the above equation starting from some initial guess until it converges on a root. I've prepared just such subroutines for this example. The first one is shown in Example 9-5 and it is used to evaluate the function under consideration at some given x-value. ## Example 9-5. Function Fx
This subroutine is actually a The next subroutine, shown in Example 9-6, is a ## Example 9-6. Function dFx
The final required subroutine is shown in Example 9-7 and actually implements Newton's method, making periodic calls to ## Example 9-7. Newton's method
Notice again that this subroutine,
Several variables are declared upon entering the function. These include Next, these variables are initialized. The A few things take place within the When the To call this function from a spreadsheet cell, you could enter something like To capture the other roots, you could change your initial guess. Changing the initial guess to 0.5 yields the root at x = 1.0. Again, changing the initial guess to 1.75 yields the root at x = 1.549. ## Secant methodAn alternative to Newton's method is the secant method
. The secant method has the advantage that you do not need to compute the derivative of the equation under consideration. As a trade-off, you do have to supply two initial guesses so that a secant line can be constructed instead of a tangent line for making x predictions. The formula for estimating x To implement the secant method, we still need the function The VBA Function that implements the secant method for this example is shown in Example 9-8. ## Example 9-8. Secant method
This function is substantially similar to the function The remaining differences are found within the You can use =secantmethod(0, -0.1, 0.0001, 100) =secantmethod(0.5, 0.6, 0.0001, 100) =secantmethod(1.75, 1.7, 0.0001, 100) These three formulas returned the values -0.215, 1.000, and 1.549, respectively. As you see, these roots are identical to those obtained using Newton's method, and in both cases the results agree well with what you can glean by examining the plot of the example cubic equation in Figure 9-6. |