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 KT is the torque coefficient, dbolt 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.
Since the worksheet contains the torque, clamp load, and bolt diameter, the average value of KT 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 R2 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:
This tells us that for this bolt, . We can solve to find KT:
The bolt diameter is contained in cell C5. In the cell for the torque coefficient KT, you can enter:
=0.0018/C5
This gives a KT of 0.15, which is a fairly typical value for a threaded fastener.
