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

EngineerExcel

FREE EBOOK
  • About
  • Course
  • Free Training
  • Resources
  • Login

Using Excel Solver for Nonlinear Regression

Excel’s Solver add-in can be used to find the best-fit line for nonlinear data. Worksheet 07f contains the flow and pressure data that was used in a previous example to illustrate fitting with LINEST.

Delete the coefficients that are in the table from that example. For Solver to work, you’ll need to enter guess values for each coefficient, so enter 1 in all three cells. The chart will update the curve with your guess values, but it’s not a good fit. We’ll use Solver to find the optimum values for each coefficient to define a best-fit curve.

The worksheet already has columns for measured flow, measured pressure, and calculated pressure. As before, you’ll use Solver to do a least-squares fit, so you’ll also need a column for error. Insert a column beside the Pcalc column and label it Error. In the first cell of this column, calculate the difference between the calculated value and the measured value:

[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.]

=D7-C7

Double-click the fill-handle to complete the column.

Recall that the Solver needs a single cell to minimize. Add a row below the coefficients for the overall error. Enter the formula:

    =SUMSQ(E7:E29)

The SUMSQ function will sum the squares of all the error values. We’ll use Solver to minimize this cell. Go to the Data tab and choose Solver. Set the objective to the cell containing the overall error. Click the button beside Min to minimize this cell.

[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.]

You can minimize the error by changing all three coefficients simultaneously. In the box for the variable cells, select the three coefficients (currently all set to 1). No constraints are needed. Leave the algorithm set to GRG Nonlinear and click Solve.

The coefficients that Solver found are not the same as those that LINEST found. That’s because GRG Nonlinear can give different results based on the initial guess values. Since we used guess values of 1, Solver found a local minimum near those values. This wouldn’t be obvious if you were using the Solver method alone and hadn’t seen the LINEST results.

However, the coefficients Solver found are close to the LINEST results, and they give a good fit to the measured data:

The curve from the Solver fit can be used to accurately represent the measured data in subsequent calculations as necessary.

[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

How to Document Equations and References in Excel

Unit Aware Calculations in Excel

3 Steps to Working with Array Formulas in Excel

Logarithms in VBA: When a Log is not a Log

Basic Lookups in Excel with VLOOKUP and HLOOKUP

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