To solve a 6 order polynomial equation in excel

[kevlongo]

Hi everybody,

I have been trying to solve a 6 polynomial equation in excel but I have no idea how to solve that. The equation is: y = - 0.0462x^6 + 2.0357x^5 - 27.0226x^4 + 202.9109x^3 - 603.5306x^2 + 1245.5878x - 121.1210
My unknown variable, in that case, is x, and y is known. I tried to solve that equation by hand first, however it is very very hard.
Is there anyone that knows a way to solve this in excel?

Thanks

 

[joeu2004]

In B1, type a single-quote (apostrophe) and copy-and-paste the part to the right of the "=" as text. Then use ctrl+h (Find and Replace) to change "x" to "*A1", then replace the leading single-quote with "=". So the formula in B1 looks like:

=- 0.0462*A1^6 + 2.0357*A1^5 - 27.0226*A1^4 + 202.9109*A1^3 - 603.5306*A1^2 + 1245.5878*A1 - 121.121

With A1 empty or a zero, the formula should return -121.121.

Now use Solver as follows:
Set Objective: B1
To Value Of: 0
By Changing: A1

My version (Excel 2010) sets A1 ("x") to 0.10212213443648 , with about 9.48E-05 in B1 ("y"). That is very close to zero.

You might get closer to zero by setting the precision options.

Also, that formula looks like a trendline formula. If you change the trendline label format to display Number with 16 decimal places and copy that improved formula into B1, you might get a more correct value for "x" with "y" close to zero.

 

[kevlongo]

Thanks for the reply.

I know that I could solve using the solver or goal seek. But my problem is that I need to use an automatic way to get the value of x, because I am using this equation in an iteration system. Therefore, using solver/goal seek becomes impossible to solve it, as those tools only allow me to get x value entering the value of y manually.
Moreover, y value is never zero, y takes always real values.

 

[Eric W]

There are closed form formulas for quadratic, cubic, and quartic polynomials, but nothing for anything bigger. So you just can't put a formula on the spreadsheet and expect an answer. You'll need some iterative process to do so. Solver and Goal Seek are 2 Excel methods built-in. If you don't want to do that, then you'll need some other tool. There are macros that do that, which you can call. Google Newton's Method VBA.

Here is an interesting link I found:

Approximate Solutions of Polynomial Equations

Following on from the previous post, this post presents a number of methods of solving polynomial equations using approximate iterative methods in Excel. Similar methods can be used to find soluti…

newtonexcelbach.com

It has a spreadsheet you can download with a Newton's method macro in it, but it also has an interesting formula using IRR that he claims can solve polynomials of any degree. I haven't tested it, so you're on your own, but it looks promising.

Also, the usual way of writing a polynomial is by equating it to 0. If your y value is not zero, you should subtract it from the constant on the other side of the equation.

 

[joeu2004]

Moreover, y value is never zero, y takes always real values.

Of course, zero is a "real value" (real number). Presumably, you mean non-zero; perhaps even positive (in contrast to "non-negative", which still includes zero).

Moreover, you can apply Goal Seek or Solver to any value of "y" for which you want to derive "x". Simply change the "To value of" field.

But of course, you must select a target value of "y" that is actually on the curve. I had to convince myself that your curve crosses the x-axis (it does!) before suggesting that you solve for "x" for y=0. You would have to do the same with any arbitrary choice for"y".

EricW mentions using the Newton-Raphson method in a VBA "macro" (sic). I presume that he means a VBA function, aka "UDF".

That is easy to program. I just want to point out that, in this case, you can calculate an exact derivative. You do not need to use Newton's approximation, which is prone to error because it depends on the step-size.

Also, I hope you heed my suggestion to improve the precision of the coefficients, if the original trendline data is available to you. You might not need 15 significant digits, which I always use. But 4 decimal places (3 significant digits in some cases) might not be accurate enough.

Good luck!