A previous post presented a spreadsheet with functions for solving cubic and quartic equations, and this has been extended with another function solving higher order polynomials. The functions are actually very easy to use, but the documentation in the spreadsheets is quite brief, and the large number of options presented may be off-putting.
To make these functions more accessible, this post presents an example of using the cubic function, and some notes on alternatives and usage of the other functions. These examples have been added as a separate file to the download file : Polynomial.zip. As usual, the download files include full open-source code. Those interested in the included Python functions, see: Python for VBA users – 5; Using built in numpy functions.
The problem to be solved is, if we have a cubic polynomial equation of the form:
Y = aX^3 + bX^2 + cX + e
how do we find the value or values of X that satisfy this equation for known values of a, b, c, and e, and any given Y?
The procedure is:
- Rearrange the equation to the form:
aX^3 + bX^2 + cX + d = 0
by subtracting Y from both sides; that is: d = e – Y. - Enter the coefficients, a to d, in a single column or row:
- Enter the cubic function, with the range of coefficient values as the argument.
- This will return one of the three solutions to the cubic equation. To display all three solutions, plus the number of real solutions, enter as an array function:
– Select the cell containing the function, and the three cells below.
– Press the F2 key (Edit)
– Press Ctrl-Shift-Enter
The four required values will be displayed as shown below:
This is a plot of the cubic function solved. It can be seen that the three solutions are the X values where the function is equal to zero.
Some cubic equations, such as in the graph below, have only one “real” solution, and two “complex” solutions, i.e. solutions with a “real” and “imaginary” part.
If the complex solutions are required the CubicC function must be used. This function is used in the same way as Cubic, except that the output range is two columns; for the real and imaginary parts of the solution:
If it is desired to display a single solution, other than the first, this can be done with the optional Out1, and Out2 arguments, as shown below. Alternatively the built-in Excel Index function may be used.
The functions Quadratic and Quartic operate in the same way as Cubic, except that they will also return complex results, so no QuadraticC or QuarticC functions are required.
The function RPolyJT may be used as an alternative to Quadratic, Cubic and Quartic, and also for higher order polynomials. RPolyJT uses the Jenkins-Traub iterative solution, and is a little slower than the other functions, but will return results nearly instantaneously in most circumstances, and can sometimes be more accurate than the other functions.
Further notes and examples are given in the download file. If anything remains unclear, please ask.
Newton Excel Bach, not (just) an Excel Blog 
I understand how entering parameters from a fitted cubic polynomial curve provides three values for x where y equals zero, but I don’t understand how this spreadsheet can calculate a fitted, unknown x-value based upon an entered, known y. Thanks for your help.
LikeLike
Dave – That’s covered by the first step in the process where you convert:
Y = aX^3 + bX^2 + cX + e
to:
aX^3 + bX^2 + cX + d = 0
by making d = e – Y
If you solve the second equation, that gives you the X value or values for the Y value required in the first equation.
LikeLike
i have an error , invalid name, when i enter the cubic function like “=cubic(B11:b14)” i dont understand why
LikeLike
Do you have macros enabled?
If enabling macros when you open the spreadsheet does not fix it, please send a copy to my gmail account (dougaj4) and I will have a look.
LikeLike
I enabled macros in the spreadsheet I downloaded from here. But how can I use functions like cubic and solvepoly in my excel sheet?
LikeLiked by 1 person
To use the functions within the Polynomial spreadsheet you just enter them in the same way as built-in Excel functions. If that is not working for you, please give more details of the problem.
To use the functions from another spreadsheet there are several options:
1. Have the Polynomial spreadsheet also open. You can then click the Insert Function Icon from the other spreadsheet, select User Defined Functions, and then select the function you want from the list.
2. Save the Polynomial spreadsheet as an add-in, which makes accessing the functions easier.
3. Save the Polynomial spreadsheet with a new name and set up the spreadsheet as you wish. You can delete any sheets you don’t want, or keep them for reference and add new sheets.
4. Copy all the VBA code to the other spreadsheet. All the functions will then be available without needing to open the Polynomial spreadsheet.
LikeLike
Hi can you guys please send me a working cubic function excel spreadsheet? Thanks
LikeLike
No, but you can download one from the link near the top of the post, or the Downloads page.
LikeLike
hi , im Brian how do you enter and a polynomial which is in fifth root using excel solver
LikeLike
hi , im Brian how do you enter a polynomial which is in fifth root using excel solver the equation is
x^5+5x^4-20x^2-10x+2=0
LikeLike
To use solver, you would choose a cell for the x value (say A1), then in another cell enter:
=A1^5+5*A1^4-20*A1^2-10*A1+2
Then use solver to adjust the value in A1 so that the formula evaluates to zero.
But it is much quicker to use my Solvepoly function (see the SolvePoly sheet in the download file):
Enter the coefficients in separate cells, including 0 for the cube coefficient, say in A1 to E1:
1, 5, 0, -20, -10, 2
Then enter =SolvePoly(A1, B1, C1, D1, E1), and enter as an array function across 7 columns. That will return all 5 solutions, the number of real solutions, and the maximum error.
LikeLike
Pingback: Solving the Lagrangian Point equation for the Moon | Newton Excel Bach, not (just) an Excel Blog
Pingback: Solving cubic equations – background and timing | Newton Excel Bach, not (just) an Excel Blog