Solving polynomials – update

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 screenshot below shows an example of usage of the new function (included in the download file):

Polynomial1In 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.
This entry was posted in Arrays, Excel, Maths, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

1 Response to Solving polynomials – update

  1. Pingback: Using the VBA ParamArray keyword | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.