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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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**.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]