6. Algebraic equations (roots of polynomials)

Examples of solving algebraic equations (finding roots of polynomials) by XLPack are shown below.

Example

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.

Published: Oct 10, 2015 (Last update: Oct 13, 2019) | Tutorial
Top