## roots of a cubic equation

For the discussion of math. Duh.

Moderators: gmalivuk, Moderators General, Prelates

### roots of a cubic equation

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.)
I don't know what to do for you

uncivlengr

Posts: 1207
Joined: Fri Nov 14, 2008 10:35 pm UTC
Location: N 49°19.01 W 123°04.41

### Re: roots of a cubic equation

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

http://office.microsoft.com/en-us/excel ... 09019.aspx
"Labor is prior to, and independent of, capital. Capital is only the fruit of labor, and could never have existed if labor had not first existed. Labor is the superior of capital, and deserves much the higher consideration." - Abraham Lincoln
Cleverbeans

Posts: 883
Joined: Wed Mar 26, 2008 1:16 pm UTC

### Re: roots of a cubic equation

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.
I don't know what to do for you

uncivlengr

Posts: 1207
Joined: Fri Nov 14, 2008 10:35 pm UTC
Location: N 49°19.01 W 123°04.41

### Re: roots of a cubic equation

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.

Talith
Proved the Goldbach Conjecture

Posts: 844
Joined: Sat Nov 29, 2008 1:28 am UTC
Location: Manchester - UK

### Re: roots of a cubic equation

Talith wrote:The biggest problem with this method is probably in factoring the cubic, ie finding the b_i s.
Why should this be so difficult?
In the future, there will be a global network of billions of adding machines.... One of the primary uses of this network will be to transport moving pictures of lesbian sex by pretending they are made out of numbers.
Spoiler:
gmss1 gmss2

gmalivuk
Archduke Vendredi of Skellington the Third, Esquire

Posts: 19450
Joined: Wed Feb 28, 2007 6:02 pm UTC
Location: Here, There, Everywhere (near Boston, anyway)

### Re: roots of a cubic equation

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.

Talith
Proved the Goldbach Conjecture

Posts: 844
Joined: Sat Nov 29, 2008 1:28 am UTC
Location: Manchester - UK

### Re: roots of a cubic equation

No need to do all that.

If x1 is a root of x3 + a2x2 + a1x + a0, then
x3 + a2x2 + a1x + a0 = (x - x1)(x2 + (a2 + x1)x + a1 + a2x1 + x12)
In the future, there will be a global network of billions of adding machines.... One of the primary uses of this network will be to transport moving pictures of lesbian sex by pretending they are made out of numbers.
Spoiler:
gmss1 gmss2

gmalivuk
Archduke Vendredi of Skellington the Third, Esquire

Posts: 19450
Joined: Wed Feb 28, 2007 6:02 pm UTC
Location: Here, There, Everywhere (near Boston, anyway)

### Re: roots of a cubic equation

Approximate Solutions of Polynomial Equations in Excel.

PM 2Ring

Posts: 2624
Joined: Mon Jan 26, 2009 3:19 pm UTC
Location: Mid north coast, NSW, Australia

### Re: roots of a cubic equation

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

Talith
Proved the Goldbach Conjecture

Posts: 844
Joined: Sat Nov 29, 2008 1:28 am UTC
Location: Manchester - UK

### Re: roots of a cubic equation

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.
Last edited by uncivlengr on Mon Aug 02, 2010 1:58 pm UTC, edited 1 time in total.
I don't know what to do for you

uncivlengr

Posts: 1207
Joined: Fri Nov 14, 2008 10:35 pm UTC
Location: N 49°19.01 W 123°04.41

### Re: roots of a cubic equation

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.

Talith
Proved the Goldbach Conjecture

Posts: 844
Joined: Sat Nov 29, 2008 1:28 am UTC
Location: Manchester - UK

### Re: roots of a cubic equation

uncivlengr wrote: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 x1 is a root of the cubic, then x13 + a2x12 + a1x1 + a0 = 0, i.e, -x13 - a2x12 - a1x1 = a0

If you expand the quadratic*root formula gmalivuk gave you, the only constant term is -x13 - a2x12 - a1x1, i.e, a0.
Syrin

Posts: 291
Joined: Thu May 24, 2007 7:10 pm UTC

### Re: roots of a cubic equation

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!
I don't know what to do for you

uncivlengr

Posts: 1207
Joined: Fri Nov 14, 2008 10:35 pm UTC
Location: N 49°19.01 W 123°04.41

### Re: roots of a cubic equation

uncivlengr wrote: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.

uncivlengr wrote: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:
http://en.wikipedia.org/wiki/Casus_irreducibilis wrote: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:

PM 2Ring

Posts: 2624
Joined: Mon Jan 26, 2009 3:19 pm UTC
Location: Mid north coast, NSW, Australia

### Re: roots of a cubic equation

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]
Chazs

Posts: 17
Joined: Sun Dec 20, 2009 7:04 am UTC

### Re: roots of a cubic equation

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.
DougAJ4

Posts: 3
Joined: Mon Aug 02, 2010 10:31 am UTC

### Re: roots of a cubic equation

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.)
ftww

Posts: 25
Joined: Sat Dec 12, 2009 11:05 am UTC

### Re: roots of a cubic equation

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).
DougAJ4

Posts: 3
Joined: Mon Aug 02, 2010 10:31 am UTC

### Re: roots of a cubic equation

DougAJ4 wrote: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).

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?

PM 2Ring

Posts: 2624
Joined: Mon Jan 26, 2009 3:19 pm UTC
Location: Mid north coast, NSW, Australia