O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

8.6. Fitting Nonlinear Curves Using Solver

Problem

You'd like to perform a curve fit using a model (an equation) that's not included in Excel's suite of curve fit functions and trendlines.

Solution

Perform a least-squares curve fit using Solver.

Discussion

Solver is a fantastic Excel add-in that allows you to perform constrained optimization calculations. Solver uses the nonlinear Generalized Reduced Gradient optimization algorithm developed by Leon Lasdon and Allan Waren. Go to the Tools → Solver menu to open the Solver dialog box shown in Figure 8-10.

Solver dialog box

Figure 8-10. Solver dialog box

If you don't see the Solver menu item, then go to Tools → Add-Ins...and look for Solver in the available add-ins list. Select its checkbox to make it available.

Solver allows you to choose a target cell and either maximize it, minimize it, or attempt to set its value to some specified value by changing values in a given range of cells. The controls in the Solver dialog box allow you to set these parameters. After setting these parameters in the Solver dialog box, press Solve to initiate the iterative calculation. Solver will iterate until it finds a solution or until certain limits are reached (these effectively put the brakes on the iterative calculation so it will not continue forever). You can set these limits by opening the Options dialog via the Options button.

The target cell could be any cell containing ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required