Saturday, February 4, 2017

Solving a Cubic Equation With Excel 2016

  Although complex numbers appear to be surreal and seem to involve something like Zeno's paradoxes they are quite useful. One can use Word 2016 to illustrate how one can use Cardan's method to solve a more general cubic equation. We first note that the coefficients of a cubic equation can be expressed in terms of its roots. We don't need a general coefficient for the cubic term since it can be eliminated by division.

One can also show that the coefficients are related to the sum of powers of the roots but this doesn't appear to be particularly useful.

One can use the mean of the roots to eliminate the coefficient, A, of the squared term in the equation by making the following substitution.

Then Cardan's method can be used to find an auxiliary equation for the solution for y.

One can solve this equation for u3 using the quadratic formula and find the cube roots to obtain solutions for u. If either B' or C' are zero the equation for u can be simplified further but we will assume that they are not. This method can be used in Excel 2016 employing the engineering functions to find the roots of a cubic equation.

After a little difficulty I was able to get an Excel scatter chart to plot these roots on a complex plane.

In the plot above the point z is equidistant from the three roots. Such a point only exists if the roots do not have a common line going through them. It can be determined as follows using the method of least squares. The asterisks indicate complex conjugates.

The expression for u above can be used to find z.

No comments: