95 L1.03: Section 2
Section 2: Using Solver in the Tool menu to find the best-fit parameters for a model
The “Solver” add-in can be used with an appropriately-formatted spreadsheet to tell the computer to do automatically what you did by hand in the earlier model-fitting exercises – adjust the parameters until the model is a good as it can be. Solver uses the numerical value of the sum-of-squared-deviations indicator (computed in cell H8 in the example spreadsheet) to decide on the best parameters.
When you use Solver for modeling, you will set up a modeling worksheet exactly as the previous example, then use Solver to tell the computer to minimize the cell containing the goodness-of-fit indicator by adjusting the cells containing the parameters. The software then systematically searches for the parameter settings that result in the smallest standard deviation.
The Solver routine does not know anything about modeling — it simply changes the numbers in the cells you identify (G3 and G4 in this case), searching for values that produce the result you ask for (minimization) in the target cell that you identify (H8). This procedure finds the best model because of the way the worksheet is set up, with the sequence of connections where the parameters in column G are used in the model formulas in column C, which determine the data-model deviations in column D and thus the squared deviations in column E, which are added to form the sum of squared deviations indicator.
Since Solver will default to the last values used, once you start using it you will often find that the settings are already correct, so that you can just press “Solve” to get the best-fit model parameters for the current set of data. If the computer does not find a solution when fitting a model, this usually means that you forgot to set the “Min” option, so that the program is instead trying to find a maximum, an impossible task in this case because there is no limit to how far away the model can get from the data.
Usually Solver will find a correct solution regardless of the initial values of the parameters. However, it will be more dependable if you start with the parameters having values that put the model graph in the same general region as the data points. Otherwise the formula may have a value so large or small that the computer cannot handle it correctly. In any case, it is important to look at the graph of the fitted values to make sure Solver worked correctly—after the computer does the tedious part, it’s your turn to do the thinking that is needed.
Example 3: Use Solver to find the formula of the best-fit exponential model for the worksheet modified in Example 1 to include a sum-of-square deviations value.
- Select the cell containing the sum-of-squared-deviations value (cell H8).
- Select Solver from the Tools menu (if it is not there, select Add-Ins and add it). The dialog box should show the selected cell (e.g., “H8”) in the Set Target Cell section at the top. If not, enter it.