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 