However, the coefficients Solver found are close to the LINEST results, and they give a good fit to the measured data: This wouldn’t be obvious if you were using the Solver method alone and hadn’t seen the LINEST results. Since we used guess values of 1, Solver found a local minimum near those values. That’s because GRG Nonlinear can give different results based on the initial guess values. The coefficients that Solver found are not the same as those that LINEST found. Leave the algorithm set to GRG Nonlinear and click Solve. In the box for the variable cells, select the three coefficients (currently all set to 1). You can minimize the error by changing all three coefficients simultaneously. Click the button beside Min to minimize this cell. Set the objective to the cell containing the overall error. The SUMSQ function will sum the squares of all the error values. Add a row below the coefficients for the overall error. Recall that the Solver needs a single cell to minimize. In the first cell of this column, calculate the difference between the calculated value and the measured value:ĭouble-click the fill-handle to complete the column. Insert a column beside the Pcalc column and label it Error. As before, you’ll use Solver to do a least-squares fit, so you’ll also need a column for error. The worksheet already has columns for measured flow, measured pressure, and calculated pressure. We’ll use Solver to find the optimum values for each coefficient to define a best-fit curve. The chart will update the curve with your guess values, but it’s not a good fit. For Solver to work, you’ll need to enter guess values for each coefficient, so enter 1 in all three cells. Worksheet 07f contains the flow and pressure data that was used in a previous example to illustrate fitting with LINEST.ĭelete the coefficients that are in the table from that example. Excel’s Solver add-in can be used to find the best-fit line for nonlinear data.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |