Nonlinear Fits and Excel Solver Tutorial PDF

Preview

Summary

This document teaches you how to make the data fit your model when you have a non linear model....

Description

Non-Linear Fits & Excel Solver Tutorial Linear vs Non-Linear Data Fits The equation y = Ax  + B is a linear model relationship between y  and x . To fit the data, we write a sum for χ2 , and minimize it by taking derivatives with respect to A  and B , and setting them equal to zero. This gives 2 equations in 2 unknowns. The equations are linear, so we can solve them by matrix inversion. The uncertainties of A  and B are the square-roots of the diagonal elements of the inverted matrix. What if the physics model is y  = Ax 2 + Bx + C ? We can still write a sum for χ2 , take derivatives with respect to A, B, and C , and set them equal to zero, giving 3 equations in 3 unknowns. The equations for A, B,  and C are still linear, so we can still solve the problem by matrix inversion. The method works for higher order polynomials too. What if the physics model is y = A sin (Bx + C ) ? We can still write a sum for χ2 , take derivatives with respect to A, B,  and C, and set them equal to zero, giving 3 equations in 3 unknowns. But in this case, the equations are not linear, which makes them much harder to solve. One approach is to linearize the equations around the current guess for the parameters A, B, and C  (which requires taking second derivatives), solving those equations for the changes in the parameters, and iterating. There are two different meanings to the word “linear” in the context of data fitting. The equation y = Ax 2 + Bx + C is a non-linear relationship between y  and x , but it is a linear relationship between y and the parameters A , B, and C. The equation y = A sin (Bx + C ) is a non-linear relationship between y  and x , and also a non-linear relationship between y  and the parameters A , B, and C . If the relationship between the data and the fit parameters is linear, there is a unique minimum of the χ2 , and the fit parameters can be found by simple sums and matrix inversion. No initial guess for the parameters is required. If the relationship between the data and the fit parameters is non-linear, there are programs that can minimize χ2 , but they usually require an initial guess of the parameters, and may fail to find the minimum if the guess is poor. There may be multiple minima of the χ2 and the programs usually don’t guarantee to have found the global minimum.

1

The Excel “Solver” Excel has a “Solver” that can adjust the values in some cells to satisfy some condition on another cell, like minimizing it. If your spreadsheet has a cell that contains χ2 calculated from your data, with the fit parameters in other cells, the Solver can be used to minimize χ2 by adjusting the cells containing the fit parameters. The Solver is an “add-in” which is not present by default, but must be activated by the user. Once it is added, it will be available for all spreadsheets until removed by the user. When it is available, a “Solver” icon will be visible at the far right of the ribbon of the “Data” tab. For Excel on Windows, activate the Solver by clicking the “File” tab (far left), then “Options” (bottom) to get the Excel Options dialog box. Click “Add-Ins” near the bottom of the list on the left. Make sure “Excel Add-Ins” is selected at the bottom next to “Manage:” then click “Go..” to get the Add-Ins dialog box. Check the box (not just the name) next to “Solver Add-In” at the bottom of the list, then click “OK.” To remove the Solver, repeat the steps but un-check the “Solver Add-In” box. For Excel on Macintosh, activate the Solver by clicking “Tools” in the Mac menu at the top of the screen (above the Excel ribbon), then “Add-Ins” at the bottom of the drop-down menu, then check the box (not just the name) next to “Solver Add-In” at the bottom of the list, then click “OK.” To remove the Solver, repeat the steps but un-check the “Solver Add-In” box. To use the Solver to minimize a calculated χ2 , first click in the cell containing χ2 , then click the “Data” tab, then on the right end of the ribbon click the “Solver” icon. This brings up the “Solver Parameters” dialog box. The address of the χ2 cell you clicked should already be in the “Set Objective:” box at the top; if not, type the cell address there. The Solver default is to maximize, which is not what we want. Click the “Min” radio-button to make sure you are minimizing χ2 . In the “By Changing Variable Cells” box, type the cell-range containing the parameters. For example, if the parameters are in cells Q1 through Q4, type “Q1:Q4”. If there is anything in the “Subject to the Constraints:” box, select it and click “Delete.”

2

Below the “Constraints” area, un-select the “Make Unconstrained Variables Non-Negative” box. The default for the Solver is to only consider positive values of the variable cells, which gives misleading results if any of the variable cells should be negative. In the “Select a Solving Method:” box, make sure “GRG Nonlinear” is selected, not “LP Simplex” or “Evolutionary.” When you click “Solve” or hit Return, the Solver will adjust the parameter cells to minimize the χ2 cell. The values in the cells will change, and also any graph that depends on those cells. If there is no error, the “Solver Results” dialog box will appear. The default is “Keep Solver Solution” which will leave the adjusted values in the cells. The other option “Restore Original Values” goes back to the starting point.

Uncertainties and Chi-Square Increase Once we have found the minimum χ2 , any change in any parameter will increase it. If there is only one fit parameter, and fit is linear, it can be shown that the χ2 will increase by exactly 1 unit if we change the fit parameter by its uncertainty, either upward or downward from the value that minimized the χ2 . If there is more than one fit parameter, it’s a bit more complicated, even for a linear fit. Changing one parameter away from the minimum will increase χ2 , but changing another parameter may undo most of the increase. Changing one parameter alone by its uncertainty will often increase χ2 by much more than 1 unit. However simultaneously changing the other parameters by the right amounts will lower the χ2 back to 1 unit more than it was at the minimum. The off-diagonal elements of the covariance matrix contain the information about how the other parameters need to be adjusted. In a non-linear fit, the relationship between the χ2 increase and the parameter uncertainties is more complicated. Most non-linear data-fitting programs calculate a matrix as a natural step in the iterative minimization process. That matrix is essentially the covariance matrix, and we can of course calculate the square roots of the diagonal elements. But even if there is only one parameter, the χ2 increase may be different from 1 when we change the parameter by that amount. It may even be different if we change the parameter by the same amount in opposite directions.

3

For non-linear data fits, the parameter value change that increases χ2 by exactly 1 unit from the minimum, adjusting all other parameters as needed, is often defined as the parameter uncertainty. This often makes the uncertainty different in the positive and negative direction. Finding Uncertainties With the Excel Solver The Excel Solver does not calculate parameter uncertainties automatically. But there is a trick to make it calculate them, using the relationship between the uncertainty and the χ2 increase, and the ability of the Solver to obey constraints. We tell the Solver to minimize or maximize one parameter, with the constraint that the χ2 must be exactly 1 unit larger than it was at the minimum, and allowing it to adjust the other parameters. You must first find the minimum χ2 . It is recommended that you make reference copies of the parameter values and χ2 value before continuing. Solver “objectives” must be formulas, so to find the uncertainty on for example cell Q4, make another cell, perhaps R4, containing the formula “=Q4” . Click the “Data” tab then the “Solver” on the right end of the ribbon. The “Solver Parameters” dialog box will probably have the setup from minimizing the χ2 . Change the “Set Objective” box to the cell with formula for the parameter (R4 in this example). Use the “Min” radio button to find the negative uncertainty, the “Max” button to find the positive uncertainty. The “By Changing Variable Cells” box should be left containing all the parameters. To the right of “Subject to the Constraints:” click “Add”. In the “Add Constraint” dialog box type the cell reference of the χ2 formula, select “=” in the middle, then type the value of χ2 at the minimum plus 1.0 (as a number, not a formula). Click “OK” to go back to the “Solver Parameters” dialog box. Make sure “Make Unconstrained Variables Non-Negative” is un-checked, and “Select a Solving Method:” is “GRG Nonlinear.” These are the same as used for χ2 minimization. Click “Solve” or hit Return to find the solution. The result should be that the χ2 cell changes from the minimum to the value you typed in as the constraint, and the parameters cells will be different. In this example, Q4 (and consequently R4) should be lower than it was at the minimum.

4

You can repeat the process with the “Max” radio button to find the positive uncertainty.

5...