Problem
You'd like to generate a bestfit straight line for a set of data.
Solution
Use Excel's chart trendline feature to perform a linear curve fit of your data. Plot your data using an XY scatter chart (see Chapter 4). Once your chart has been created, rightclick on the data series and select Add Trendline from the popup menu.
Discussion
Linear curve fits are easily generated using the trendline feature built into Excel's XY scatter chart. Once you've plotted your data using an XY scatter chart, you can generate a trendline that will be displayed on your chart, superimposed over your data. You can also include the resulting equation for the bestfit line on your chart.
Consider the data shown in Table 81. This data represents experimentally obtained angular deflections of an angular spring resulting from prescribed applied torques.
Applied torque 
Measured deflection 

(Nm) 
(degrees) 
1.993 
1.547 
1.411 
1.178 
1.440 
1.167 
0.443 
0.318 
0.428 
0.416 
0.444 
0.327 
0.016 
0.035 
0.013 
0.032 
0.049 
0.036 
0.830 
0.762 
0.854 
0.687 
0.851 
0.698 
1.730 
1.412 
1.703 
1.399 
1.698 
1.419 
2.044 
1.675 
2.063 
1.624 
2.034 
1.627 
This data should exhibit a linear trend when plotted. (Assuming the spring obeys Hooke's law.) Indeed this is the case, as is revealed by plotting this data on an XY scatter chart. Once that chart has been created, rightclick on the data series and select Add Trendline from the popup menu. This will open the Add Trendline dialog box shown in Figure 81.

Select the Linear Trend/Regression type as shown. Before pressing OK, click the Options tab (see Figure 82).
Select "Display equation on chart" and "Display Rsquared value on chart." The former will display the resulting bestfit equation on your chart, while the latter will also include the Rsquared value, allowing you to assess the goodness of the fit (see Recipe 8.7). Press OK to go back to your chart and see the resulting trendline.
Figure 81. Add Trendline dialog box
Figure 82. Add Trendline Options tab
Figure 83 shows the resulting bestfit line for the example data contained in Table 81. The bestfit line is shown as the dashed line in Figure 83. The original data points are shown as dots. Clearly the data follows a straight line trend very well.
Figure 83. Linear fit

The equation for the bestfit line is displayed in the upperright of the chart. The Rsquared value for the trendline is very close to 1, indicating a good fit.

In practice, you could use the slope from this bestfit line to estimate the spring constant for the spring being tested. You have to be careful with units here, because we've plotted deflection versus torque, resulting in units for the slope of degrees per Nm.
In reality, you'd probably want the reciprocal of this value, to yield a slope with units of Nm per degree, following the standard units for spring constants. You can force your bestfit line to go through the origin by specifying the intercept on the Options tab of the Add Trendline dialog box (see Figure 82). Perhaps a better approach would be to plot the angular deflections on the horizontal axis with the torque data on the vertical axis, and then fit a line through the points.

See Also
See Chapter 4 for recipes covering creating and manipulating charts in Excel. Take a look at Recipe 8.7 to learn more about the Rsquared value of trendlines. Also, check out the other recipes in this chapter to learn about alternative methods of performing linear curve fitting.
Using Excel
Getting Acquainted with Visual Basic for Applications
Collecting and Cleaning Up Data
Charting
Statistical Analysis
Time Series Analysis
Mathematical Functions
Curve Fitting and Regression
Solving Equations
Numerical Integration and Differentiation
Solving Ordinary Differential Equations
Solving Partial Differential Equations
Performing Optimization Analyses in Excel
Introduction to Financial Calculations
Index