How to fit Quadratic and Cubic Curves with LINEST in Excel

The Excel LINEST function is normally used to fit a straight line to data points.  However, using this little-known technique you can also fit higher-order curves.  This example shows how to use LINEST to fit Quadratic and Cubic Curves to data.

This example comes from the sample spreadsheet LINEST-2-3.xlsx in our Excel for Engineers online course.  You can type the example in yourself from the data supplied below, or order the file free of charge from us by email.

1.     Example: Pump Curves

Here is an example from Mechanical Engineering.  Specifically, pump design.  From experimental data, we have the Pump Curves for one impeller of a multi-stage high-lift pump.  These curves are for Head (m) and Power (kW) for given values of flow (in ℓ/s).

To interpolate between the points, we want to fit curves to the flow/head and flow/power points.  We surmise that quadratic (y=ax²+bx+c) or cubic (y=ax³+bx²+cx+d) curves may fit.

To make it easier to understand the formula later, we will name some ranges.  Select cells B6:B11.  In the Name Box on the left of the formula bar, type the word Flow and press Enter.

Similarly, name cells C6:C11 as Head and cells D6:D11 as Power.

2.     Fitting a Quadratic Curve with LINEST:

Let us calculate the quadratic (2nd power) LINEST Coefficients for Head:

LINEST is an array function.  We first select the cells for the results.  Then type the formula.  Finally press Ctrl+Shift+Enter to enter it as an array formula.

The quadratic equation has three coefficients (a, b and c in y=ax²+bx+c).  Select the three empty cells G2:I2.

Type =LINEST(Head, Flow^{1,2},TRUE, FALSE) and press Ctrl+Shift+Enter.  You should see three coefficients.

We will put the equation for Head (fit²) in F6 to line up with the first row of values.  The formula is:

=$G$2*B6^2 +$H$2*B6 +$I$2

Copy it down to cells F7:F11.

Now to see the error (difference between experimental values and the fitted curve).  In cell G6, enter the formula =C6-F6 and copy down as far as row 11.  Some values will be positive and some negative.

3.     Fitting a Cubic Curve with LINEST:

Now let us calculate the cubic (3rd power) LINEST Coefficients for Head:

The cubic equation has four coefficients (a, b, c and d in y=ax³+bx²+cx+d).  Select the 4 empty cells F3:I3

Type =LINEST(Head, Flow^{1,2,3},TRUE, FALSE) and press Ctrl+Shift+Enter.  You should see four coefficients.

The equation for Head (fit³) in H6 is:

=$F$3*B6^3 +$G$3*B6^2 +$H$3*B6 +$I$3

Copy it down to cells H7:H11.

Now to see the error (difference between experimental values and the fitted curve).  In cell I6, enter the formula = =C6-H6 and copy down as far as row 11.

4.     Fitting the Curves for Power

The above gave us the curves for Head.  You can produce similar equations for Power.

Use cells L2:N3 for LINEST for the quadratic equation, and K3:N3 below them for LINEST for the cubic equation.

The quadratic and cubic equations for Power can then go in cells K6:K11 and M6:M11 respectively.

Their error calculations can go in columns L and N respectively.

The spreadsheet will then look like this (except for row 12, which we will add below):

5.     More Array Formula Magic

The Maximum Error values on row 12 we can also get with array formulas

Here, we have a series of positive and negative error readings (above or below expected values).  We want to know the largest, irrespective of its sign.  We could do a MAX and a MIN of the values and then pick the largest absolute (ABS) value of the two.  But the magic of Array Formulas lets us tell Excel to do an ABS of each value individually, and then find the MAX of all of them, in just one nested formula:

Here it is. In G12, type =MAX(ABS(G6:G11)) and press Ctrl+Shift+Enter.

(To enter an Array Formula, first select all the cells in which the formula must appear.  Type the formula, then press Ctrl+Shift+Enter.  –Not just Enter as you would normally.)

In this case, unlike LINEST above, we are putting the array formula in a single cell.  Nevertheless, we need an array formula because the ABS must be applied to each individual cell first.  Try entering the formula =MAX(ABS(I6:I11)) in cell I12 by just pressing Enter and notice the error!

(Footnote: Recent versions of Excel (2021 onwards) seem to be more forgiving and also accept Enter in this case.  Excel 2016 gave a #VALUE! Error.)

Copy the array formula to I12, L12, and N12.

If you look at the cell contents on the formula bar, it appears inside braces {curly brackets}, thus: {=MAX(ABS(E2:E7))}.  The braces show that this is an Array Formula.  You never type the braces, and they disappear while you are editing the formula.

6.     Finally, a Chart

To make sense of the results, we can also graph them: