• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Course
  • Free Training
  • Resources
  • Login

Linear Regression in Excel with Charts

There are many different tools available in Excel for linear and nonlinear regression. The method described in this post can be used on linear data in a chart. Excel can quickly find the equation of the line that fits your data using chart trend lines.

Assume we have some 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:

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

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.

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

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

Primary Sidebar

Featured Posts

The Excel CONCATENATE Function for Engineers

5 Smart Ways to Use Excel for Engineering

Finding an Equation Root Graphically in Excel

Protecting Excel Worksheets and Workbooks

How to Fit an Equation to Data in Excel

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in