Newton Excel Bach, not (just) an Excel Blog

Brent’s Method is a refinement of Newton’s Method for the numerical solution of any equation with one variable.  The User Defined Function (UDF) QuadBrent was described in:  The Inverse Quadratic Method 3 – Brent’s Method

Following a recent question, I have updated the function and added some simple examples and additional documentation, linking to the Wikipedia article.  The new spreadsheet can be downloaded from:

ItSolve.zip

The main update is that in addition to calling a VBA function to solve, the equation may be entered as text on the spreadsheet.  There is also a new function, QuadBrentT, for which this option is the default.

A simple example, solving a trigonometric equation, is shown in the screen-shot below (click on any image for full size view):

The function to be solved is entered as text in cell B4.  Using the original QuadBrent function, the default function type is the name of a VBA function.  To solve a text function on the spreadsheet, the new “EvalTxt” argument must be set to True.  The full list of arguments is now:

QuadBrent(Func, Ak, Bk, Coefficients, YTol , MaxIts , Subr, XTol, EvalTxt , ParamA , SolveFor )

• Func: Name of VBA function, or function entered as text on the spreadsheet
• Ak: Minimum x value
• Bk: Maximum x value
• Coefficients: value of fixed parameters used in the function; default = none
• Ytol: solution tolerance on Y value; default = 1E-12
• MaxIts; maximum iterations, default = 20
• Subr: solution type; 1= Inverse Quadratic (default), 2 = Quadratic
• XTol: solution tolerance on X value; default = 1E-12
• EvalTxt: evaluate the entered text string; default = False
• ParamA; list of fixed parameters (required if EvalTxt = True only); default = none
• SolveFor: variable character (required if EvalTxt = True only); default = “x”

For the new QuadBrentT function the argument order and defaults are changed for greater convenience when evaluating text functions on the spreadsheet:

QuadBrentT(Func, Ak, Bk, ParamA, Coefficients, SolveFor, YTol , MaxIts , Subr, XTol )

Arguments are as listed above, except EvalTxt is no longer required, since it is always true.

In this example the solution to the equation could of course easily be found using the ASin function, but the method is easily applied to more complex equations, as shown below:

In this case two additional array arguments are required: the symbols representing constant values, and their associated values.

The next example illustrates the use of the QuadBrent function calling a VBA function to solve the same equation as above.  The code for VBA_1 is:

Function VBA_1(Coeffa As Variant, x As Double)
VBA_1 = Coeffa(1, 1) * Sin(x) - Coeffa(2, 1) * Cos(x) - Coeffa(3, 1)
End Function

Note that all required data must be passed as a single variant array, followed by the function variable.

The algorithm for Brent’s Method (taken from the Wikipedia page) is shown below:

This is applied to the first simple example on the spreadsheet, showing the results for each step of each iteration:

The Wikipedia page has a more complex example, solving a cubic equation.  This is solved with the QuadBrentT function below, and also with the Cubic UDF:

The step by step method is also shown on the spreadsheet:

This can be compared with the listing of the results at each iteration on the Wikipedia page:

Not that, at the time of writing, steps 7 to 9 have corrected results shown at the end of the line, and these corrected values agree with the spreadsheet results shown above.