Finding the Roots of an Equation with Excel VBA

MS Excel is a convenient tool for finding the roots of equations. General recommendations for solving equations of arbitrary degree can be formulated as follows:

• Tabulate the given function over a certain interval to identify (localize) the roots of the equation (a sign change in the function values). Sometimes, repeated tabulation is necessary for more accurate estimates.
• After localizing the roots, set the maximum number of iterations and the tolerance for root computation (go to the File tab on the ribbon and select Options; in the Excel Options window, select the Formulas category on the left, and on the right — the Calculation options group).
• Compute the roots directly using the Goal Seek tool (go to the Data tab on the ribbon, and in the Data Tools group select What-If Analysis → Goal Seek).
• Plot the graph of the function for clarity (choose the required chart from the Charts group on the Insert tab of the ribbon).

Example

Suppose we need to find all the roots of the equation:

f(x)=x⁵+2x⁴+5x³+8x²−7x−3=0.

The solution of this equation is shown:

• Approximate tabulation of the function

f(x)=x⁵+2x⁴+5x³+8x²−7x−3=0.

over the interval [−10;10][-10; 10].

1. • Enter the argument values for the interval [−10;10][-10; 10] with a step of 1 into cells A12:A32.
   • In cells B12:B32, compute the function f(x)f(x).
     Formula for cell B12:

=A12^5+2*A12^4+5*A12^3+8*A12^2-7*A12-3

• A chart can be built for the tabulated function.
• From the calculations, we determine that f(x)f(x) changes sign on the interval [−3;1][-3; 1]. We then perform more precise tabulation on this interval.
  • Enter the argument values for the interval [−3;1][-3; 1] with a step of 0.1 into cells D12:D52.
  • Compute the function values in column E. Formula for cell E12:

=D12^5+2*D12^4+5*D12^3+8*D12^2-7*D12-3

• The results of precise tabulation show 3 sign changes on the interval [−3;1][-3; 1], which indicates the existence of 3 roots of the equation f(x)=0.
• Using the Goal Seek tool, determine the first root of the equation. Place the cursor in cell E14 (or E15), then go to the Data tab on the ribbon, and in the Data Tools group select What-If Analysis → Goal Seek.
  This tool gives the first root of the equation:

x1=0.789295735548989

• Similarly, the other two roots are calculated:

x2=−0.328038079539342,x3=2.07299390058983.