The previous post in this series presented iterative methods to solve polynomial equations using direct or inverse quadratic interpolation. These methods have two disadvantages:

- In some circumstances the function may converge very slowly, or not at all.
- The name of the function to be solved must be hard coded into the solution function

The user defined function QuadBrent overcomes both of these problems:

- Brent’s Method is used to use different interpolation techniques (quadratic, linear, or bisection) through the course of the solution.
- The name of the function to be solved is an argument of the UDF, (using the technique described in this recent post) so the QuadBrent function may be used to solve any equation that can be evaluated with a VBA function.

In addition the quadratic interpolation may be performed either by the Inverse Quadratic Method (the default), or by using Muller’s Method.

The function is entered as shown below:

** =QuadBrent(FunctionName, Ak, Bk, Coefficients, Max error = 1E-14, Max iterations = 20, Subroutine = 1, Xtol 1e-14)**

- FunctionName is the name of a VBA function evaluating the equation to be solved.
- Ak and Bk are the lower and upper bounds to the solution value.
- Coefficients is a range or array of values that will be passed to FunctionName
- Subroutine = 1 for Inverse Quadratic method or 2 for Muller’s method

The function returns a single column array with three rows:

- The solution to the equation
- The number of iterations
- The error in the function value for the given solution

In order to display all three rows the function must be entered as an array function:

- Enter the function as normal.
- Select the cell containing the function and the two below.
- Press F2
- Press Ctrl-Alt-Enter

The QuadBrent function has been added to the ItSolve Functions2.xls spreadsheet, including full open source code. The functions previously presented, QuadMuller and QuadSolve2 have also been modified to accept the input the name of a function to be evaluated.

Use of the Quadbrent function, along with the two earlier functions, is shown in the screen shots below:

Note that in the second example the function crosses the x axis at x = -3, then touches the axis, but does not cross, at x = 1. The QuadBrent function has found the solution at x = -3, but the QuadMuller function has failed to converge:

The spreadsheet also includes an on-spreadsheet implementation of Brent’s Method used to solve the equation shown above. The solution output is shown below, and may be compared with the detailed description of the evaluation given in the Wikipedia article:

Nicely done, thanks for this.

I see that Richard Brent is also based down under at ANU, there’s a good tradition of math in Oz including recent field medalist Terry Tao. Did a stint of tutoring at UTS myself a few years back – good times.

LikeLike

Pingback: Calling a function as a variable – another example « Newton Excel Bach, not (just) an Excel Blog

Pingback: Composite Beam Spreadsheet | Newton Excel Bach, not (just) an Excel Blog

Pingback: Composite Beam Spreadsheet – 2 | Newton Excel Bach, not (just) an Excel Blog

Pingback: Composite Beam Spreadsheet 3 | Newton Excel Bach, not (just) an Excel Blog

Pingback: Daily Download 21: Assorted Solvers | Newton Excel Bach, not (just) an Excel Blog

Pingback: Faster Biaxial Bending | Newton Excel Bach, not (just) an Excel Blog

Pingback: Solving non-linear equations with two or more unknowns – 1 | Newton Excel Bach, not (just) an Excel Blog