The Excel Solver can be used to perform a least squares regression. For most situations, you can do regression using either trendlines in the chart or the LINEST function. However, there may be situations where you want to find a best fit manually. Even if you never need to do a manual fit, this section will help you understand what’s going on in the other regression methods.
To do a manual least squares fit with Solver, you need to do three things first: enter a starting guess for the coefficient of x, calculate y-values based on that guess value, and calculate the error between the calculated y-values and the measured y-values. Then you can use Solver to find the coefficient that minimizes the sum of the squared errors.
Worksheet 07c contains the torque problem from the previous sections. First, enter a guess value of 0.001 for the slope. Next, add two columns in the Data section: one for Tcalc (calculated torque) and one for Error, both in units of N-m.
We’ll fill in both of these columns with array formulas. Select all of the cells in the calculated torque column. This column will be equal to the guess value for the slope times the clamp load:
=C8*B13:B30
Use Ctrl-Shift-Enter to enter it as an array formula.
The error will simply be the difference between the calculated torque and the measured torque. Select all the cells in the Error column and enter the formula:
=D13:D30-C13:C30
Type Ctrl-Shift-Enter again.
Solver needs one cell to minimize, so we’ll add a row to the Calculations table to calculate the overall error. Select the four cells horizontally below that table, right-click, and choose Insert. Select Shift Cells Down and click OK.
Label the new row Error. For the overall error, we’ll calculate the sum of all the errors squared using the SUMSQ function.
=SUMSQ(E14:E31)
The tables should now be set up:
You’ll notice that the initial error is quite high. That will be the target cell for Solver to minimize. It will do so by adjusting the slope value.
Go to Data > Solver.
Set the objective to the single error cell, C9, and choose to minimize it.
The slope will be the variable cell, so select cell C8 in the next box.
No constraints will be necessary, but the slope is clearly positive, so you can leave the non-negative constraint option selected. The GRG Nonlinear algorithm is fine for this problem, so click Solve. Click OK when Solver comes back with a result. Not surprisingly, it’s the same value that the LINEST function gave in the previous section:
To see the results, select the first three columns in the Data table, and insert a scatter chart using the Quick Access Toolbar. The blue points are the measured data, and the green points are the best fit line.
As expected, the best fit line represents the measured data accurately. You may not need to use this method often, but it’s a powerful method to know in case you do. It’s helpful to understand how a least squares fit is performed.
