Solving Quadratic, Cubic, Quartic and higher order equations; examples

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:
    polyroots2-1
  • 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:

polyroots2-2
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.
polyroots2-3
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.
polyroots2-5
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:
polyroots2-4
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.
polyroots2-6
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.
polyroots2-7

Further notes and examples are given in the download file. If anything remains unclear, please ask.

This entry was posted in Arrays, Excel, Link to Python, Maths, Newton, UDFs, VBA and tagged , , , , , , , , , . Bookmark the permalink.

8 Responses to Solving Quadratic, Cubic, Quartic and higher order equations; examples

  1. Dave Lykins says:

    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.

    Like

    • dougaj4 says:

      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.

      Like

  2. mhd says:

    i have an error , invalid name, when i enter the cubic function like “=cubic(B11:b14)” i dont understand why

    Like

    • dougaj4 says:

      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.

      Like

      • Paridhi Agarwal says:

        I enabled macros in the spreadsheet I downloaded from here. But how can I use functions like cubic and solvepoly in my excel sheet?

        Liked by 1 person

        • dougaj4 says:

          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.

          Like

  3. Jhit says:

    Hi can you guys please send me a working cubic function excel spreadsheet? Thanks

    Like

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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.