6. Algebraic equations (roots of polynomials)
Examples of solving algebraic equations (finding roots of polynomials) by XLPack are shown below.
Now, we will solve the following equation.
x5 + 2x3 + 2x2 - 15x + 10 = 0
This can be factorized as follows.
(x - 1)2(x + 2)(x2 + 5) = 0
Then, the solutions are 1(multiple root), -2, and ±√5i.
Two methods using worksheet function WRpzero2 and VBA subroutine Rpzero2 are described.
Solving problem with worksheet function
Enter the data of coefficients ai of the equation into the appropriate cells in the worksheet, and select (n + 1) x 3 cells where the solution is to be entered (x(r), x(i) and s(i) in the figure). Click fx in the formula bar. Then select WRpzero2 worksheet function.
Supply the appropriate values for the requested parameters N (order of the equations, e.g. 5 in this example) and A() (range of coefficients).
Then press Ctrl+Shift+Enter.
Now, we’ve got the five solutions correctly. The most right column (s(i)) shows the error bounds.
For the multiple root (1), the accuracy is dropped to about half of those for other roots. It should be noted that, in solving algebraic equations, the effective number of digits of the computed m multiple root value becomes about 1/m of that of single root.
Solving problem with VBA program
Let’s solve the same example problem by VBA program. The following is an example code using VBA library subroutine Rpzero2.
Sub Start() Const NMax = 10 Dim N As Long Dim A(NMax) As Double, XR(NMax) As Double, XI(NMax) As Double, S(NMax) As Double Dim IFlag As Long, Info As Long, Iter As Long, I As Long '--- Input data N = 5 For I = 0 To N A(I) = Cells(4 + I, 1) Next '--- Compute roots of equation IFlag = 0 Call Rpzero2(N, A(), XR(), XI(), IFlag, S(), Info, Iter) Cells(9, 2) = Info Cells(9, 3) = Iter '--- Output roots For I = 0 To N - 1 Cells(4 + I, 2) = XR(I) Cells(4 + I, 3) = XI(I) Cells(4 + I, 4) = S(I) Next End Sub
After entering the data into the appropriate cells, the same result with above was obtained by running the macro Start.