roots of a cubic equation

[uncivlengr]

I'm looking to solve for the smallest positive root of a cubic function. I've set up an excel sheet that takes all the properties of the column and determines the equation to be solved, but at that point, I rely on goalseek to determine the answer.

I don't like this for a number of reasons - one, I don't know that it's the smallest positive root, and two, I have to run goalseek every time I update the properties. I can write a VB macro to run the goalseek every time a value is changed, but I'd rather get away from goalseek and macros completely.

Now there's the cubic equation for calculating roots, but I don't know how complex numbers work in Excel. I do know that for this application, there are always three real roots, and it's always the middle one that I need to obtain. The lowest is always negative, the middle always positive, and the highest is only slightly larger than the middle (hence my distrust of goalseek).

So here's the question: knowing there are no complex roots, is there a way to calculate a particular root without complex numbers? Is there a way to simplify the formulas for cubic roots given the information known to use only real numbers?

Alternatively, is there a way to get excel to calculate the roots using the standard equations?

(not that it matters, but it's for determining the flexural-torsional buckling strength of an asymmetric structural column. Why in Pete's name they gave engineers cubic equations to solve in design checks I'll never know. I'm actually going to just use a symmetric section to simplify this particular issue and get it out the door, but I'm just curious about the original problem now.)

[Cleverbeans]

Excel has a complex number package, I found this with Google.

http://office.microsoft.com/en-us/excel ... 09019.aspx

[uncivlengr]

I did find that, but it's horribly complicated to work with - you need to use special functions for every operation, and I'm not even sure how you'd take the cube root of the complex number using the few excel functions.

Since all the roots are real, the imaginary parts need to cancel out at some point - I just need to figure out where that happens, and do that before trying to put the values in.

[Talith]

Glancing quickly at wikipedia, it looks like you can always find one root without using complex numbers. Call this root x_1. if you have the equation in the form x^3+a_2x^2+a_1x+a_0=0. You should be able to take a factor of x-x_1 out of the cubic leaving you with (x-x_1)(b_2x^2+b_1x+b_0)=0. Solving the quadratic should then be pretty easy. The biggest problem with this method is probably in factoring the cubic, ie finding the b_i s.

[gmalivuk]

The biggest problem with this method is probably in factoring the cubic, ie finding the b_i s.

Why should this be so difficult?

[Talith]

I didn't say it was a huge problem, just probably the hardest part. I've not done much with excel in the past either, so I'm not sure how easy it'd be to program polynomial long division.

[gmalivuk]

No need to do all that.

If x₁ is a root of x³ + a₂x² + a₁x + a₀, then
x³ + a₂x² + a₁x + a₀ = (x - x₁)(x² + (a₂ + x₁)x + a₁ + a₂x₁ + x₁²)

[PM 2Ring]

You might find this helpful:

Approximate Solutions of Polynomial Equations in Excel.

[Talith]

That's some clever stuff I'll have to remember that. [EDIT]that was at gmalivuk[/EDIT]

[uncivlengr]

Thanks for the help - I'll give it a shot.

I've gone through the calculation for x1, and wouldn't you know it, I ended up with imaginary numbers - the trick I used, though, is the fact that I knew the imaginary parts needed to cancel each other out, so I just took those parts out of the equation. Hopefully that'll work!

edit: I tried that equation, gmalivuk, and it seems to be off by a constant - there's no a0 in the remaining quadratic, so I'm guessing the issue is somewhere there.

[Talith]

The wiki page says that if the cubic discriminant is negative, (2b^3 − 9abc + 27a^2d)^2 − 4(b^2 − 3ac)^3 <0, you have to do a little more work. I just imagined that it would only be negative if one of your solutions was complex though, and you said that all three of your roots should be real.

[Syrin]

edit: I tried that equation, gmalivuk, and it seems to be off by a constant - there's no a0 in the remaining quadratic, so I'm guessing the issue is somewhere there.

If x₁ is a root of the cubic, then x₁³ + a₂x₁² + a₁x₁ + a₀ = 0, i.e, -x₁³ - a₂x₁² - a₁x₁ = a₀

If you expand the quadratic*root formula gmalivuk gave you, the only constant term is -x₁³ - a₂x₁² - a₁x₁, i.e, a₀.

[uncivlengr]

Right - the issue is probably an issue with excel rounding values, then... the constants coming out of the equation are pretty large, but the discrepancy between the true roots and the ones I'm getting are small enough.

Thanks!

edit: aaaand the contractor just called and decided to just get an extra crane for the operation instead of using the bracing system I was working on, so none of the work I spent my Sunday doing matters anyway!

[PM 2Ring]

Right - the issue is probably an issue with excel rounding values, then... the constants coming out of the equation are pretty large, but the discrepancy between the true roots and the ones I'm getting are small enough.

Maybe.

edit: aaaand the contractor just called and decided to just get an extra crane for the operation instead of using the bracing system I was working on, so none of the work I spent my Sunday doing matters anyway!

Don't you just hate that!

But anyway, for next time:

In algebra, casus irreducibilis (Latin for "the irreducible case") is one of the cases that may arise in attempting to solve a cubic equation with integer coefficients with roots that are expressed with radicals. Specifically, if a cubic polynomial is irreducible over the rational numbers and has three real roots, then in order to express the roots with radicals, one must introduce complex-valued expressions, even though the resulting expressions are ultimately real-valued.

Because of this fact, mathematicians realized that they had to deal with complex numbers. Sure, they arise in quadratics, too, but they were able to treat such quadratic equations as "monsters" with no application to the real world. They didn't have that option in the case of cubics.

Also, if you need to take the cube root of a complex number, the best way is to convert to polar form. You can solve them by Newton's method, but it's not guaranteed to stabilize - in fact you can draw a pretty fractal based on this:

[Chazs]

Why not create a simple Newton's Method formula for the zeros of the cubic x^3+b x^2 +c x +d, and then have your first "guess" the inflection point which is always at x = -b/3.

Your formula would be: x - (x^3 + b x^2 +c x +d)/(3x^2+2 bx +c) where the x's would actually be a cell reference, i.e., A1.

Put the coefficients in B1 C1 and D1, and -B1/3 into A1. Newtons formula (above) goes into A2 will need absolute cell references ($B$1) for the coefficients, and a relative reference A1 for the x's. Grab the fill-handle of A2 and fill down 10 cells. From here on out just change the three coefficients for other cubics.

Voila!!!! ce n'est pas difficile!

CHUCK

[EDIT]Okay, a previous post included a link to a site using the Newton-Raphson method with Excel (I didn't see it before), but why do they need to make it so difficult. Sheesh.[/EDIT]

[DougAJ4]

EDIT]Okay, a previous post included a link to a site using the Newton-Raphson method with Excel (I didn't see it before), but why do they need to make it so difficult. Sheesh.[/EDIT]

What's difficult? The third method in the spreadsheet was exactly the same as yours, except it was a quartic rather than cubic, and it used Sumproduct to save a bit of typing.

But the easy (and quicker) way to do it is to use the UDFs, which will return all the roots (real or complex) and only require entering one function.

[ftww]

Note that complex numbers are only required if we want a solution in radicals, but we can stay completely in the real domain when all three roots are real. Here's how the process goes, although I don't turn it into explicit formulae here.

Start with the general cubic a_3x^3 + a_2x^2 + a_1x + a_0 = 0, and divide by a_3 to render it monic: x^3 + b_2x^2 + b_1x + b_0 = 0.

Perform the substitution x = y - b_2/3 to eliminate the quadratic term, yielding a reduced cubic equation y^3 + c_1y + c_0 = 0.

Now the bit that is not as well-known as it should be: Set \alpha = \sqrt{-4c_1/3}. If \alpha is not real, then there are complex roots (which you have ruled out), so we can assume that \alpha is real. Perform the substitution y = \alpha z and multiply by appropriate constants to produce an equation in this form: 4z^3 - 3z = d_0.

If there are three real roots, then we will have that |d_0| \le 1. If this is not the case then some of the roots are complex. So again, we assume that |d_0| \le 1.

One final substitution! Write z = \cos(\theta), and the above equation simplifies to \cos(3\theta) = d_0. Take the inverse cosine to find 3\theta, divide by three (and add 2\pi/3, 4\pi/3) to get the three values of \theta, and backsubstitute to find the original solutions.


Example: Consider the equation 2x^3 - 5x^2 + x + 2 = 0. First we divide by 2 to make it monic: x^3 - 5/2x^2 + x/2 + 1 = 0. Now we set x = y + 5/6 to eliminate the x^2 term, getting: y^3 - 19/12y + 7/27.

We have c_1 = -19/12, so \alpha = \sqrt{-4*c_1/3} = \sqrt{19}/3. Setting y = z\sqrt{19}/3 and multiplying by 108\sqrt{19}/361 gives: 4z^3 - 3z = -28\sqrt{19}/361.

So we have \cos(3\theta) = -28\sqrt{19}/361, whence 3*theta \approx 1.916-, and \theta \approx 0.639-, 2.733-, 4.827+. Rolling back the values, we get x = \alpha \cos(\theta_i) + 5/6 = 2, -0.5, 1. (And it is easy to check that the original equation has roots of -1/2, 1, 2.)

[DougAJ4]

ftww - the blog that PM2Ring linked to (and apparently I am not allowed to link to) contains a spreadsheet with a Cubic function using the same method as you posted (and also a CubicC function that will find complex roots).

[PM 2Ring]

ftww - the blog that PM2Ring linked to (and apparently I am not allowed to link to) contains a spreadsheet with a Cubic function using the same method as you posted (and also a CubicC function that will find complex roots).

Well, you'll be able to post links to your heart's content once you make a couple more posts, Doug.

On a slight tangent, does anyone know if there's any way to compute the cube roots of a general complex number exactly (ie without using trig or iteration), or is it ruled out by Galois theory?