• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Excel Course
  • VBA Course
  • Free Training
  • Login

Nonlinear Curve Fitting in Excel

I’ve discussed linear regression on this blog before, but quite often a straight line is not the best way to represent your data. For these specific situations, we can take advantage of some of the tools available to perform nonlinear regression or curve fitting in Excel.

Remember our old friend LINEST? Although LINEST is short for “linear estimation”, we can also use it for nonlinear data with a few simple tweaks.

Polynomial Curve Fitting in Excel

Let’s say we have some data of pressure drop vs. flow rate through a water valve, and after plotting the data on a chart we see that the data is quadratic.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

nonlinear regression excel

Even though this data is nonlinear, the LINEST function can also be used here to find the best fit curve for this data. For a polynomial equation, we do that by using array constants.

An advantage to using LINEST to get the coefficients that define the polynomial equation is that we can return the coefficients directly to cells. That way we don’t have to manually transfer them out of the chart.

Since the equation is quadratic, or a second order polynomial, there are three coefficients, one for x squared, one for x, and a constant. So we’ll need to start by creating a space to store the three coefficients for the equation.

Using LINEST for Nonlinear Regression in Excel

The LINEST function returns an array of coefficients, and optional regression statistics. So we’ll need to enter it as an array formula by selecting all three of the cells for the coefficients before entering the formula.

If the cells containing the flow and pressure data are named “flow” and “pressure”, the formula looks like this:

=LINEST(pressure, flow^{1,2},TRUE, FALSE)

nonlinear curve fitting in excel

The known y’s in this case are the pressure measurements, and the known x’s are the flow measurements raised to the first and second power. The curly brackets, “{” and “}”, indicate an array constant in Excel. Basically, we are telling Excel to create two arrays: one of flow and another of flow-squared, and to fit the pressure to both of those arrays together.

Finally, the TRUE and FALSE arguments tell the LINEST function to calculate the y-intercept normally (rather than force it to zero) and not to return additional regression statistics, respectively.

Since it’s an array formula, we need to enter it by typing Ctrl+Shift+Enter.

The function then returns the coefficients of x2 and x as well as a constant (because we chose to allow LINEST to calculate the y-intercept).

The coefficients are identical to those generated by the chart trendline tool, but they are in cells now which makes them much easier to use in subsequent calculations.

For any polynomial equation, LINEST returns the coefficient for the highest order of the independent variable on the far left side, followed by the next highest and so on, and finally the constant.

A similar technique can be used for Exponential, Logarithmic, and Power function curve fitting in Excel as well.

Fitting a Logarithmic Curve to Data

logarithmic function fit to excel data

A logarithmic function has the form:

We can still use LINEST to find the coefficient, m, and constant, b, for this equation by inserting ln(x) as the argument for the known_x’s:

=LINEST(y_values,ln(x_values),TRUE,FALSE)

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Of course, this method applies to any logarithmic equation, regardless of the base number. So it could be applied to an equation containing log10 or log2 just as easily.

Finding the Coefficients of a Best-Fit Exponential Curve

curve fitting exponential function in excel

An exponential function has the form:

It’s a little trickier to get the coefficients, a and b, for this equation because first we need to do a little algebra to make the equation take on a “linear” form. First, take the natural log of both sides of the equation to get the following:

Now we can use LINEST to get ln(a) and b by entering ln(y) as the argument for the y_values:

=LINEST(ln(y_values),x_values,TRUE,FALSE)

The second value returned by this array formula is ln(a), so to get just “a”, we would simply use the exponential function:

Which, in Excel, translates to:

=EXP(number)

Fitting a Power Function to Data

curve fitting power function

A power function curve can be fit to data using LINEST in much the same way that we do it for an exponential function. A power function has the form:

Again, we can “linearize” it by taking the base 10 log of both sides of the equation to obtain:

With the equation in this form, the LINEST function to return b and log10(a) can be set up like this:

=LINEST(LOG10(yvalues),LOG10(xvalues),TRUE,FALSE)

Since the LINEST function returns b and log10(a), we’ll have to find a with the following formula:

In Excel, that formula is:

=10^(number)

That’s it for now. As you can see, there are a number of ways to use the LINEST function for nonlinear curve fitting in Excel.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Primary Sidebar

Featured Posts

Solving a System of Simultaneous Linear Equations in Excel

Using Excel VLOOKUP to Pull Tabular Data into Calculations

Error-Free and Easily Verified Calculation Tools

Increase Efficiency with Keyboard Shortcuts

Rounding Numbers in Excel

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in