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