Solving cubic and quartic equations with Excel

Although in earlier posts (such as this one) I have referred to some User Defined Functions (UDFs)  for solving cubic and quartic equations, I just realised recently that I haven’t actually talked about them here, and since they are in most cases the most practical way of dealing with these equations, that ought to be fixed.

An “on sheet” solution to quadratic, cubic and quartic equations can be found in the spreadsheet Polynom.xls by Alex Tomanovich, which can be downloaded from the ExcelCalcs site.

Polynom.xls Cubic solution

The solutions in polynom.xls provide an excellent explanation of the method of solution, but are not very flexible or practical for solving a large number of equations.  For that purpose I have written four UDFs:

  • Quartic
  • CubicC
  • Cubic
  • Quadratic

Each function, other than Cubic, returns the real and complex roots of a polynomial equation with coefficients specified in either a single column range or a single row range.  Cubic is for use when only the real roots are required, and is a little faster than CubicC.  The functions may either return a single specified root of the equation, or if entered as an array function, will provide all roots (including real and imaginary parts for complex roots) and the number of real and complex roots.  The spreadsheet may be downloaded from Polynomial.zip (including full open source code).  The screenshot below shows input and output:

Quartic.xls input and output, click for full size view

The cubic and quartic functions are based on Fortran code from The homepage of AK Kraska.

About these ads
This entry was posted in Excel, Maths, Newton, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

15 Responses to Solving cubic and quartic equations with Excel

  1. Pingback: Complex Numbers and Solving Quartic Polynomial Equations | Newton Excel Bach, not (just) an Excel Blog

  2. Pier says:

    Hi Dougaj64;

    da questo link http://www.box.com/shared/kbo9xkhckb puoi scaricare una mia funzione personalizzata in excel per trovare le soluzioni di una equazione cubica a coefficienti reali. Ha una impostazione diversa rispetto a quella che hai presentato tu.

    Ciao e grazie mille per il tuo interessantissimo blog.
    Pier

  3. dougaj4 says:

    Hi Pier- thanks for the download; interesting use of “types” for the complex numbers. I’ll have a closer look when I have time.

  4. very useful, I was looking for this for a while, thanks a lot

  5. Pingback: Daily Download 19: Solving polynomials | Newton Excel Bach, not (just) an Excel Blog

  6. Jean-Jacques Millepied says:

    Hi Doug,
    I’m not a brilliant mathematician and am not conversant with VBA. Please forgive.
    Your Polynomial.xlsb spreadsheet has a strange behaviour when using the first worksheet (Poly).
    I use it to find the roots of quartic polynomials (in order to subsequently recompose the polynomial as the product of two quadratic polynomials).
    I tested some very simple cases, among which those two:
    a=1 b=5 c=6 d=5 e=1 => Excel returns the #VALUE! error
    a=1 b=5 c=8 d=5 e=1 => VBA returns “Solution not found”
    Any idea why?
    Thanks a lot in advance.
    Jean-Jacques

  7. dougaj4 says:

    I don’t know specifically why these values cause a problem, other than that the Quartic algorithm can give poor results in some cases. The Quartic function will solve for those values (with very small error) if you increase e to 1.00000000000001, but a better solution would be to use the RPolyJT function, which uses an iterative solution, which is more stable.

    See https://newtonexcelbach.wordpress.com/2011/01/13/solving-higher-order-polynomials/

    and latest spreadsheet with these functions is now: http://interactiveds.com.au/software/Polynomial.zip

    • dougaj4 says:

      I have now had a closer look at the Quartic function, and found that the problem was coming from the CubicC function, which had a couple of problems causing errors for certain specific inputs. These have now been fixed and the revised version is available in the Polynomial.zip file linked above.

  8. Jean-Jacques Millepied says:

    Thanks Doug! It works for me.
    Jean-Jacques

  9. Pingback: Polynomial Update | Newton Excel Bach, not (just) an Excel Blog

  10. dougaj4 says:

    See https://newtonexcelbach.wordpress.com/2012/10/30/polynomial-update/

    Note that the latest update is in Polynomial.zip, not Quartic.zip

  11. Jean-Jacques Millepied says:

    As you mentioned before. Thanks again.

  12. Luis Edo says:

    Hi, everybody

    I tried to use this worksheet for calculating a cubic equation and doesn’t work.
    here thje coeficients:
    a= 0.75
    b= -47.40775
    c= 43.60547
    d=-8.0729

    What could happens?

    • dougaj4 says:

      Luis – I get 3 real solutions of 0.256, 0.675 and 62.28.

      The latest version of this function is now in Polynomial.zip. The version in quartic.zip gives the same results for the coeficients given, using the cubic function, but gives a wrong value when usiing the quartic function with a specifed root.

      I have changed the download link to download the latest version.

  13. Pingback: Solving Quadratic, Cubic, Quartic and higher order equations; examples | Newton Excel Bach, not (just) an Excel Blog

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s