Excel can quickly find the equation of the line that fits your data using chart trend lines.

In a worksheet, there are measurements that were collected on a 12-mm diameter bolt. The clamp load and the torque were measured to determine the torque coefficient of the bolt. Torque and clamp load are related by this equation:

where K_{T} is the torque coefficient, d_{bolt} is the bolt’s diameter, and F is the clamp load. One of the reasons why you may need to know this torque coefficient would be to get proper specifications for an assembly torque for a bolted joint.

[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.]

Since the worksheet contains the torque, clamp load, and bolt diameter, the average value of K_{T} can be calculated by fitting a line to the data that was collected. **Select **the clamp load and torque data and **create a scatter chart **using either the shortcut in the **Quick Access Toolbar **or **Insert **> **XY Scatter**. The first type of scatter chart that shows only data points works well for this purpose.

The data does appear to have a linear relationship between torque and clamp load. To get the equation that fits the data, **right-click on the series **and select **Add Trendline**. This will add a best-fit line to the chart. By default, the linear trendline is selected in the task pane, so there’s no need to change that. However, lower down in the task pane are some additional options. Select the checkbox next to **Set Intercept **and make sure it is set to **zero**. This is physically realistic for this problem – if the clamp load were zero, the bolt torque would also be zero.

Select the next two checkboxes to **display the equation on the chart **and **display the R ^{2} value on the chart**. This will give us the slope of the trendline and indicate how well the line fits the data.

Now, the trendline and its equation are displayed on the chart:

[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.]

This tells us that for this bolt, . We can solve to find K_{T}:

The bolt diameter is contained in cell C5. In the cell for the torque coefficient K_{T}, you can enter:

** =0.0018/C5
**

This gives a K_{T} of 0.15, which is a fairly typical value for a threaded fastener.

[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.]