## 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.

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

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:

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

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

### 24 Responses to Solving cubic and quartic equations with Excel

1. 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

Like

2. 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.

Like

3. beatcorrosion says:

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

Like

4. 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
Any idea why?
Jean-Jacques

Like

5. 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.

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

Like

• 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.

Like

6. Jean-Jacques Millepied says:

Thanks Doug! It works for me.
Jean-Jacques

Like

7. dougaj4 says:

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

Like

8. Jean-Jacques Millepied says:

As you mentioned before. Thanks again.

Like

9. 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?

Like

• 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.

Like

10. Soren Test says:

Thank you for a useful Excell sheet.
I work with stadard curves in biochemistry and this is really helpful – after I have asked Excell to give the best line throgh my results – to get the equation.

One question. If I have a column of numbers (which will be the d values) that I want to enter into the calculation how do I get Excel to: “take one number put it into “the d cell” and return the data from “I20″ to another cell – and then do the same for a column of data”. The same a, b and c for all – as they are from a standard curve that is used to read the data against.

Soren Test

Like

• dougaj4 says:

The functions as written did not have an easy way to do this, because the coefficients are in a single range, so I have written a new function where the coefficients are listed separately. The new file (Polynomial.xlsb) can be downloaded from:
http://interactiveds.com.au/software/Polynomial.zip

The new function is called SolvePoly() and there is an example of how to use it on the SolvePoly sheet.

I will post with more details in the next day or two, but if you have any questions, please ask.

Like

11. Soren Test says:

Thank you for this improvement – and thank you for your positive attitude.

Beeing a bit irritating – Could you possibly give me the possiblity to enter more than 10 values in the column for d values? In practice the ideal for me would be to have a column where I can enter 48 values. This happens to be the number I mazimally get of results from one assay run.

Like

• dougaj4 says:

You can have more than 1 million extra rows! Just copy any one of the output rows (columns D to G), and paste in as many rows as you want. You can also have your input ranges and output anywhere in the workbook, including on a new sheet if you want.

You might find it useful to read the page on “Using Array Functions and UDFs” (main menu, under the picture at the top).

Also if you are not familiar with using absolute and relative cell addresses, have a look in the Excel help.

Feel free to ask on anything.

Like

12. Pier says:

Dougs;
ho modificato il mio file Cubica.xls in modo da poter risolvere 100 equazioni cubiche contemporaneamente..
Considerato che Soren Test ha difficoltà ad usare le funzioni per le matrici (Array Functions) ho pensato di predisporre cento righe dove poter inserire i coefficienti a, b, c d che si desiderano.

Il file lo si può scaricare dal seguente link: https://app.box.com/s/env9vlaaivonf9qy3184
ed è di libero utilizzo (free).

P.S.: Seguo sempre con moloto interesse il tuo blog e colgo anche questa occasione per ringraziarti.
Approvo completamente il tuo pensiero sulla condivisione.

Saluti.
Pier

Like

13. Carlos Alberto Guimarães de Sá says:

UMA EQUAÇÃO DO SEGUNDO GRAU PARA RESOLVER.

Primeira providência: Eliminar o termo quadrático e fazer x = (y-b)/3a
x = y – 3/3 # x = y – 1. Importante para se achar x.
Agora onde tiver x , fazer y – 1
(y-1)^3 + 3( y – 1)^2 -22( y – 1) -24 = 0
y^3 – 3y^2 + 3y – 1^3 + 3( y^2 – 2y +1^2) – 22 y + 22 -24 = 0
y^3 – 3y^2 + 3y – 1 + 3y^2 -6y +3 -22y +22 -24 = 0
Fica assim: y^3 +3y -1 -6y +3 -22y + 22 – 24 = 0
y^3
+3y -6y -22y = -25y
-1 +3 +22 – 24 = 0.
Equação reduzida: y^3 -25y = 0
Está no fim: y^3 = 25 # y=raiz quadrada de 25 # y= 5 # x = 5-1 = 4
x = 4. Esse é o número procurado.
Vamos comparar com o nosso raciocínio anterior:
[ 4 ( 4+1) (4+2) ] + [8 ( 4) +(4+1) + (4+2) ]
4* 5*6 = [ 8 ( 4 + 5 + 6 ) ]
120 = 8 ( 15 )
120 = 120
A soma dos quadrados : 4^2 + 5^2 + 6^2 = 16 + 25 + 36 = 77.

Like

14. Jong Long says:

What methods did you use in making the Cubic root solver? What are the steps I can do to get to a complete Cubic root solver?

Like

• dougaj4 says:

The cubic function in the download spreadsheet is already a complete solver for cubic polynomials, but if you want to see how it works the code is open source, and quite short.

Like

15. Princeton University says:

It’s surprising to find on wordpress.com a resource so precious about equations.

We will note your page as a benchmark for Solving cubic and quartic equations with Excel .

We also invite you to link and other web resources for equations like
http://equation-solver.org/ or https://en.wikipedia.org/wiki/Equation.
Thank you ang good luck!

Like

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