The polynomial spreadsheet (details here) provides functions to solve polynomial equations of any order; using an “exact” method for up to quartic, and an iterative procedure for higher orders. The input for the functions requires the equation coefficients to be in a continuous column or row range. This is convenient for many cases, but there are times when it is necessary to enter each coefficient separately.
I have now added a new function to the spreadsheet, which has the coefficients entered separately, and calls the appropriate solver function, depending on the number of coefficients. The new function, including full open source code, may be downloaded from Polynomial.zip.The screenshot below shows an example of usage of the new function (included in the download file):
In this example three coefficients of a cubic equation are constant, but the fourth varies. The solution to the equation can be set up easily as follows:
- Enter the values for coefficients a to c in any convenient range.
- Enter the values for coefficient d in a column
- In the cell adjacent to the top of the d column, enter the SolvePoly function:
=solvepoly($B$12,$B$13,$B$14,C12). Note the $ signs, making the first three addresses absolute (i.e. they will not change when the function is copied). - To display the three solutions and the number of real roots enter the function as an array function: select the function cell and the adjacent three cells; press F2; press ctrl-shift-enter.
- These four cells may now be copied to the clipboard and pasted over as many rows as required. Note that the first three coefficients always refer to the fixed range, but the d coefficient changes as it is copied down.
Newton Excel Bach, not (just) an Excel Blog 
Pingback: Using the VBA ParamArray keyword | Newton Excel Bach, not (just) an Excel Blog
Hi Doug, thanks for sharing this useful tool. I’m trying out the functions and had a few questions with cubic and quartic.
Are the real roots outputs always in ascending order and if so, how are the imaginary roots ordered?
Is there a way for the function to output horizontal cell arrays (ie rightwards instead of downwards)? I like to arrange spreadsheets horizontally so I can drag the formulas down the page (fits more on screen).
If using cubic(), How to distinguish between zeros that represent imaginary roots and actual zero as the real root? There is a rare case where the local maxima/minima is tangential to (0,0).
For quartic() only wanting real roots, is there a simple automatable way to know whether the outputted roots are real or to select only real roots when used as a single cell formula? I am guessing there is no option to make it output #N/A or non-numeric whenever a root is imaginary.
LikeLike
Please ignore the question about horizontal output – I discovered SolvePoly that outputs columns (was looking at the Examples spreadsheet when I made that post).
LikeLike
Hi Z,
Yes, real roots are listed first, in ascending order, then complex roots with the real part in ascending order, and for equal real parts the positive imaginary part is listed first.
All the functions, except Cubic, return a 2 column array, with the roots listed first, then the number of real and complex roots, in the left and right column respectively. If you want to return just one value, with an error message if it is a complex root, the easiest way would be to check if the imaginary part was zero in the adjacent cell, and use an IF function to return a message if it wasn’t. Something like:
=IF(quartic(Coefficient Range, 1, 2)=0,””,”Complex root!”)
An alternative would be to write your own VBA function to call the polynomial function, and check for the non-zero imaginary part in VBA. Then return either the real part or a text message. All the VBA code is open source, so it’s easy to add your own if you are familiar with VBA.
A similar approach would work for the Quartic function.
And of course you are free to modify the code for your own purposes if you want to.
LikeLike
Thanks so much Doug. I was able to get exactly what I wanted with a combination of IF and INDEX functions. The ability to automatically solve polynomial functions excites me. For so many decades, engineering publications have opined on the laborious, iterative nature of solving high order polynomials but now it is trivial. I can see this becoming my favourite tool for the years to come!
Can you tell us some of the more interesting uses of this spreadsheet you’ve heard over the years? beam deflection equations and pile embedment depths come to mind but I’m sure there are many more uses.
LikeLike
Hi Doug a quintic with coefficients 5.333333333 11.66666667 -6.666666667 -26.66666667 -13.33333333 -2.666666667 outputs the single real root last.
LikeLike
It seems the code using iterative approximations was returning results in the order they were found so real roots could be anywhere.
I have added a routine to loop through the results and select all the real results, then the complex results.
The new code is uploaded to the same link as before.
Only very quick checking, so let me know if you find any more problems.
LikeLike
=SolvePoly(1,0,0,1) outputs the negative imaginary part preceding the positive imaginary conjugate. Quad2({1;0;1}) returns the negative imaginary root first. (no complaints as I only use reals).
=SolvePoly(1,0,0,0,-1) Quartic real roots not ascending
=SolvePoly(1,3,-15,-19,30,0) Quintic real roots not ascending
This version of SolvePoly accepts array ranges too, you decide if it is worth implementing as you are the one hosting and maintaining the code https://pastebin.com/NUWNVYNp
Otherwise these may give some ideas about sorting the roots. https://pastebin.com/qw9ePub1 and https://pastebin.com/u0jsNmjE
LikeLike
Local maximina/minima at (0,0) was a bad example. a=b=c=1 and d=0 is a better example, with one real root at 0 and 2 complex roots.
I also noticed that cubic() outputs 1.851275209519E-17 instead of 0 when that is a real root (not sure if bug or intentional to distinguish with complex roots). I also realised it is impossible for there to be exactly one complex roots to cubic functions.
Also the cubic() Polynomial-Examples chart seems to zoom in too much with this combination of coefficients, as does the poly tab in the Polynomials.
LikeLike
SolvePoly does not work when numbers are typed in manually, ie SolvePoly(1,A1,B1,C1). Its a bit silly but useful for when the leading coefficient is 1. Admittedly, I can than type 1 into a random cell and reference that cell.
Also incompatible with negatives in front of the cell references SolvePoly(A1,-B1,-C1,-D1). Again, I can use intermediate helper cells to workaround this, but would it be an easy change for SolvePoly work in these use cases?
Thank you for your time and dedication Doug.
LikeLike
Thanks for picking that up. It was quite an easy fix. I have also added 3 functions, QuarticR, CubicR, and QuadraticR, that only return real roots, and return an error message if the requested root is complex or doesn’t exist.
I will copy to a new blog post in the next few days, but you can download a trial version from:
Polynomial-dev3.zip
LikeLike
Thanks again Doug, I can even use MAX on those new formulas.
I already have a working excel formula to extract reals from SolvePoly, but any chance of getting SolvePolyR too so the cell formulas can be simpler? not a big deal if its a non-trivial thing to code.
I did notice that Cell C35 in even the current SolvePoly sheets says “Num_Coeff must be >= 3” because SolvePoly does not accept array ranges SolvePoly(A2:A7) and
LikeLike
I have now posted an updated spreadsheet at VBA polynomial functions update. I think this covers all the issues you raised, but if you find anything else, please let me know.
LikeLike
Thank you Doug.
Sorry to be a pain but are you able to include array input handling to SolvePolyR()? This code does that and also returns an output if there are no real roots (I haven’t given much thought whether the output “no real roots”, #N/A or something else). https://pastebin.com/t8xR0PpZ
LikeLike
Pingback: VBA polynomial functions update | Newton Excel Bach, not (just) an Excel Blog