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
Pingback: Brent’s Method and a daft engineer’s blog | Newton Excel Bach, not (just) an Excel Blog
Thanks for this tutorials…I am very new with Brent’s method..I have a trigonometric function I need to solve with it.can you please pick a simple trigonometric function and by hand help solve it with Brent method so I can follow?I’d be grateful..thanks
LikeLike
See: https://newtonexcelbach.wordpress.com/2017/12/06/brents-method-update-and-examples/
If anything is not clear, please ask.
LikeLike
Pingback: Brent’s Method; Update and Examples | Newton Excel Bach, not (just) an Excel Blog